Postgresql count row grouped by

Question

I have the following data in a PostgreSQL database:

id | sub1 | sub2 | sub3 | sub4 | unique |
---|------|------|------|------|--------|
1  |  a   | null | null | null |  true  |
2  |  a   | null | null | null |  true  |
3  |  a   | null | null | null |  false |
4  |  a   |  b   | null | null |  true  |
5  |  a   |  b   | null | null |  false |
6  | null | null | null | null |  true  |

I need results like this, group and count by sub, for example:

 sub1 | sub2 | sub3 | sub4 | sum | unique_sum |
  a   | null | null | null |  3  |     2      |
  a   |  b   | null | null |  2  |     1      |
 null | null | null | null |  1  |     1      |

I am stuck with this query:

    SELECT   sub1, 
         sub2, 
         sub3, 
         sub4, 
         ( 
                SELECT Count(*) AS sum 
                FROM   data 
                WHERE 
                       CASE 
                              WHEN sub1 notnull THEN sub1 = d.sub1 
                              ELSE sub1 IS NULL 
                       END 
                AND 
                       CASE 
                              WHEN sub2 notnull THEN sub2 = d.sub2 
                              ELSE sub2 IS NULL 
                       END 
                AND 
                       CASE 
                              WHEN sub3 notnull THEN sub3 = d.sub3 
                              ELSE sub3 IS NULL 
                       END 
                AND 
                       CASE 
                              WHEN sub4 notnull THEN sub4 = d.sub4 
                              ELSE sub4 IS NULL 
                       END ) AS sum, 
         ( 
                SELECT count(*) AS unique_sum 
                FROM   data 
                WHERE  UNIQUE IS true 
                AND 
                       CASE 
                              WHEN sub1 notnull THEN sub1 = d.sub1 
                              ELSE sub1 IS NULL 
                       END 
                AND 
                       CASE 
                              WHEN sub2 notnull THEN sub2 = d.sub2 
                              ELSE sub2 IS NULL 
                       END 
                AND 
                       CASE 
                              WHEN sub3 notnull THEN sub3 = d.sub3 
                              ELSE sub3 IS NULL 
                       END 
                AND 
                       CASE 
                              WHEN sub4 notnull THEN sub4 = d.sub4 
                              ELSE sub4 IS NULL 
                       END ) AS unique_sum 
FROM     data                AS d 
ORDER BY sub1, 
         sub2, 
         sub3, 
         sub4

I update logic for more realistic and detailed example, added SUM and UNIQUE SUM count. Real data is more complicated.

Answer 1

This would do it:

SELECT sub1, sub2, sub3, sub4
     , count(*) AS sum
     , count(unique_sum OR NULL) AS unique_sum
--   , count(*) FILTER (WHERE unique_sum) AS unique_sum  -- equivalent
FROM   test
GROUP  BY 1, 2, 3, 4
ORDER  BY 5 DESC, 6 DESC;  -- matches your demo result
-- ORDER BY 1, 2, 3, 4  -- or this, like in your query

Related:

• Postgres COUNT number of column values with INNER JOIN