-3

How can I count the number of somethings in each column, and rename as something?

For example - this is my table:

PATIENT

PatientNum  City
---------------------------
1           New York City
2           Boston
3           Birmingham
4           Tyler
5           Boston
6           New York City

This is my SQL query:

SELECT 
    City, COUNT(DISTINCT PatientNum) AS Freq 
FROM 
    PATIENT 
GROUP BY 
    City

Expected result

City            Freq
----------------------
Birmingham       1
Boston           2
New York City    2
Tyler            1

What is the relational algebra for this SQL query? I am new to relational algebra, thank you for help!

Update:

I have do some research and write that. Is that right?

enter image description here

FelixLoveICT
  • 17
  • 3
  • 2
    Add appropriate database name, sample data and your expected output from them. – mkRabbani Mar 29 '20 at 09:25
  • Does this answer your question? [Using group by on two fields and count in SQL](https://stackoverflow.com/questions/10380990/using-group-by-on-two-fields-and-count-in-sql) – Alireza Mahmoudi Mar 29 '20 at 12:00
  • No. I know the SQL statement. I don't know how to change it to relational algebra. – FelixLoveICT Mar 29 '20 at 12:08
  • Explain why you think that is right. [ask] Also: There are many RAs (relational algebras). They differ in operators & even what a relation is. Give operator definitions & your reference for yours. Eg textbook name, edition & page. Please [use text, not images/links, for text--including tables & ERDs](https://meta.stackoverflow.com/q/285551/3404097). Use images only for what cannot be expressed as text or to augment text. Images cannot be searched for or cut & pasted. Normal text would be enough here but if you want you can google 'unicode lowercase pi' etc & read the edit help re subscripts. – philipxy Mar 29 '20 at 23:56
  • Does this answer your question? [Query using aggregation and/or groups in relational algebra - count, max, min, etc](https://stackoverflow.com/questions/15660545/query-using-aggregation-and-or-groups-in-relational-algebra-count-max-min-e) – philipxy Mar 30 '20 at 01:22

1 Answers1

1

You need to use the group by operator, γ. The columns listed before the γ are the grouping columns, the columns listed after are your projections

So, your expression in relational algebra would be like this:

City γ City, COUNT PatientNum (PATIENT)

Relational projections always return distinct tuples.

B. Monteiro
  • 53
  • 6