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.

Erwin Brandstetter · Accepted Answer · 2019-07-01 06:08:46Z

0

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

Postgres COUNT number of column values with INNER JOIN

edited Jul 1, 2019 at 6:08

answered Jul 1, 2019 at 6:02

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgresql count row grouped by

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related