misuse of aggregate: max() when getting the max of a view in SQLITE

user_4070982 0
Viewed 52

I have created a VIEW (VISIT_VIEW) that has two columns, citizens and count(visitsDoctors) AS ToTal_Visits:

CREATE VIEW VISIT_VIEW AS
SELECT citizen, COUNT(doctor) AS Total_Visits
FROM citizensvisits
GROUP BY citizen, doctor;

That returns a lis of how many times each citizen has visited a doctor

citizen1 1
citizen1 2
citizen1 5
...
citizen3 10
citizen3 4

Then I need to create a new view that will hold the MAX of the TotalVisits and for each citizen

CREATE VIEW MAXVISITS AS
SELECT citizens,  Total_Visits AS MaxTotal_Visits
FROM VISIT_VIEW
WHERE MaxTotal_Visits  =  max("Total_Visits")
GROUP BY citizens, MaxTotal_Visits;

so when trying to

SELECT *
FROM MAXVISIT_VIEW

I get

Result: misuse of aggregate: max()

What am I doing wrong ?

edit: I ve put the VISIT_VEW code and to clarify, I need a new view that should list for each citizen's its MAX visits ie

citizen1 5
citizen3 10
sql-view sqlite
2 Answers

You can't use MAX() aggregate function directly.
You must aggregate again:

CREATE VIEW MAXVISITS AS
SELECT citizen, MAX(Total_Visits) AS MaxTotal_Visits
FROM VISIT_VIEW
GROUP BY citizen

or without the use of VISIT_VIEW, selecting directly from the table with MAX() window function:

CREATE VIEW MAXVISITS AS
SELECT DISTINCT citizen, 
       MAX(COUNT(*)) OVER (PARTITION BY citizen) AS Total_Visits 
FROM citizensvisits 
GROUP BY citizen, doctor;
user_104988280

I might be sorely mistaken, but I believe you would need to do a sub select

Something like this, but not exactly

CREATE VIEW MAXVISITS AS
SELECT citizens,  Total_Visits AS MaxTotal_Visits
FROM VISIT_VIEW
WHERE MaxTotal_Visits = (SELECT MAX(Total_Visits) FROM VISIT_VIEW)
GROUP BY citizens, MaxTotal_Visits;

There may or may not be other problems. with my solution included.

user_27302070
Related