How to filter a SQL Query using ROLLUP

Question

This query:

SELECT 1, 2, count(*) 
FROM t
GROUP BY ROLLUP (1, 2)
ORDER BY 1, 2

Shows:

1, 2 
A Null 3
A Blue 2
A Neon 1
B NULL 2
B Navy 2
C NULL 4
C Neon 2
C Blue 2

You see the sums A = 3, B = 2, and C = 4?

I want to filter to only show if the SUM is greater than 2, and all related data. So I'd see all A and all C, but not B.

If I add HAVING COUNT(*) > 2 it affects all values. I'd see lines 1 and 6.

I have also tired HAVING grouping(count(*)) > 2 but get error "Cannot perform an aggregate function on an expression containing an aggregate or a subquery." I am semi new to SQL so I don't know if this related to what I am trying to do.

Thanks!

score 1 · Accepted Answer · answered Apr 18 '19 at 18:12

use exists like below

select a.* from 
(
SELECT col1, col2, count(*) as cnt
FROM t
GROUP BY ROLLUP (col1, col2)
) a where 
 exists ( select 1 from 
   (

    SELECT 1, 2, count(*) as cnt
    FROM t
    GROUP BY ROLLUP (1, 2)

   ) b where a.col1=b.col1 and b.cnt>2)

How to filter a SQL Query using ROLLUP

1 Answers1