Search code examples
sqlsubquery

Subquery SQL , get the count of ID's based on the subquery results

I have written a sub query like this-

Select ID, count(*) as cn from xyz group by 1

Results in an output of-

ID cn
A 3
B 45

Now I am doing this query -

SELECT CASE
         WHEN temp.cn > 10 THEN Count(DISTINCT id)
       END AS cn_10,
       CASE
         WHEN temp.cn <= 10 THEN Count(DISTINCT id)
       END AS cn_9
FROM   (SELECT id,
               Count(*) AS cn
        FROM   xyz
        GROUP  BY 1) AS temp;

hoping to get an output like

cn_10 cn_9
300 400

But I keep getting this error,

SQL compilation error: [temp.cn] is not a valid group by expression

Solution

  • You can use a case expression within the aggregation like below, untested of course but does this work for you?

    select
  Count(case when cn  > 10 then 1 end) cn_10,
  Count(case when cn <= 10 then 1 end) cn_9
from (
    select id, Count(*) cn
    from xyz
    group by Id
)t;