Subquery returns more than one result

Asked

Viewed 58 times

0

Good afternoon! I have the following query that counts the total number of subscribers per course. I want to find the largest amount of subscriber according to code:

WITH CTE AS( SELECT IDPS, 
(SELECT COUNT(*) FROM CURSOS
WHERE IDAREAINTERESSE = 60) AS DIREITO, (SELECT COUNT(*) FROM CURSOS
WHERE IDAREAINTERESSE = 61) AS PEDAGOGIA, (SELECT COUNT(*) FROM CURSOS
WHERE IDAREAINTERESSE = 62) AS CONTABILIDADE FROM CURSOS) SELECT
CASE 
     WHEN DIREITO > PEDAGOGIA AND DIREITO > CONTABILIDADE THEN DIREITO
     WHEN PEDAGOGIA > DIREITO AND PEDAGOGIA > CONTABILIDADE THEN PEDAGOGIA
     WHEN CONTABILIDADE > DIREITO AND CONTABILIDADE > PEDAGOGIA THEN CONTABILIDADE
    END AS MAIOR FROM CTE

I got the result by the case, but I would like to have an easier way to get the highest value within the CTE of the subconsultas, since I only put a few courses, because there are many. I tried to make a Union all along with the max() function but it gave an error of subconsulta returning more than one value. Is there another way? Thank you.

sql case cte union-all

1 answer

2

Try this:

SELECT IDAREAINTERESSE, COUNT(*)
FROM CURSOS
GROUP BY IDAREAINTERESSE
ORDER BY COUNT(*) DESC

Browser other questions tagged sql case cte union-all

You are not signed in. Login or sign up in order to post.