Question 88991 in PostgreSQL Question posted in
The official documentation can be found here.

How to group by on two columns and count on the third column? – Postgresql

I have data in my postgres table like this.

enter image description here

I want to get a result like this.

Expected result

I tried nested query like this. I only did rows where column1 is having multiple values in column2. For example, C3, c4 have just one each, I’d like to skip that.

select column1, column2, dCount
from (select column1, column2, count(column3) as dCount
      from schema.table
      where column1 != 0
      group by column1, column2) t
group by t.column1, t.column2, t.dCount
having count(t.column2) > 0;

But I am getting 0 rows out.

Tags:

2

Answers


  1. 0

    You can do it using group by and count() and HAVING COUNT(column3) > 1 to get only records with multiple data :

    SELECT column1, column2, count(column3) as dCount
FROM supportassist.tbldeviceservice
GROUP BY column1, column2
HAVING COUNT(column3) > 1

    Demo here

    Login or Signup to reply.
  2. 0

    We can use the following two step aggregation query:

    WITH cte AS (
    SELECT column1, column2, COUNT(column3) AS dCount,
           MIN(column2) OVER (PARTITION BY column1) AS min_column2,
           MAX(column2) OVER (PARTITION BY column1) AS max_column2
    FROM schema.table
    WHERE column1 != 0
    GROUP BY column1, column2
)

SELECT column1, column2, dCount
FROM cte
WHERE min_column2 <> max_column2
ORDER BY column1, column2;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search