CODEWITHSUNDEEP
mysqlsqlcount
Sakshi Sharma
Sakshi Sharma

Reputation: 1

Cannot perform an aggregate function on an expression containing an aggregate or a subquery in MySql

I want to find out, Which course has been done by most number of students?

create table studies(
PNAME varchar(20),
INSTITUTE varchar(20),
COURSE varchar(20),
COURSEFEE int
);

insert into studies values
('Anand','Sabhari','PGDCA',4500),
('Altaf', 'Coit','DCA',7200),
('Juliana', 'Bdps','MCA',2200),
('Kamala', 'Pragathi','DCA',5000),
('Mary', 'Sabhari','PGDCA',4500),
('Nelson', 'Pragathi','DAP',6200),
('Patrick', 'Pragathi','DCAP',5200),
('Qadir', 'Apple','HDCA',1400),
('Ramesh', 'Sabhari','PGDCA',4500),
('Rebecca', 'Brilliant','DCAP',11000),
('Remitha', 'Bdps','DCS',6000),
('Revathi', 'Sabhari','DAP',5000),
('Vijaya', 'Bdps','DCA',4800);

Query attempt:

SELECT COURSE
FROM STUDIES
GROUP BY COURSE
HAVING COUNT(COURSE)= (SELECT MAX(COUNT(COURSE))
FROM STUDIES
GROUP BY COURSE)

Upvotes: 0

Views: 145

Answers (2)

Kabir Hossain
Kabir Hossain

Reputation: 3105

You can use as for single one:

SELECT COURSE FROM `STUDIES` HAVING COUNT(COURSE)

If you want for multiple:

SELECT COURSE
FROM STUDIES
GROUP BY COURSE
HAVING COUNT(*) = (SELECT MAX(subject)
                  FROM (SELECT COUNT(*) AS subject
                    FROM STUDIES
                    GROUP BY COURSE) rs
                  )

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521997

If you really wanted to make your current version work, the subquery in the HAVING clause would need a bit more work:

SELECT COURSE, COUNT(*) AS cnt
FROM STUDIES
GROUP BY COURSE
HAVING COUNT(*) = (SELECT MAX(cnt)
                   FROM (SELECT COUNT(*) AS cnt
                         FROM STUDIES
                         GROUP BY COURSE) t);

Assuming you want the course having the max count, and you don't expect duplicates, you might be able to just use a LIMIT query here:

SELECT COURSE, COUNT(*) AS cnt
FROM STUDIES
GROUP BY COURSE
ORDER BY COUNT(*) DESC
LIMIT 1;

Or, you could use RANK or ROW_NUMBER here, if your version of MySQL be 8 or later:

WITH cte AS (
    SELECT COURSE, COUNT(*) AS cnt,
           RANK() OVER (ORDER BY COUNT(*) DESC) rnk
    FROM STUDIES
    GROUP BY COURSE
)

SELECT COURSE, cnt
FROM cte
WHERE rnk = 1;

Upvotes: 1

Related Questions