Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

How to group separately by multiple columns in PostgreSQL?

I know that you can use the GROUP BY keyword to group by multiple columns, but I don’t know how to group by separate groups in one query:

Table: Humans

 id | name | surname  | year  |
----|------|----------|-------|
 1  | Egor | Krid     |  14   |
 2  | Dima | Maxnin   |  12   |
 3  | Ivan | Krid     |  12   |
 4  | Egor | Petrenko |  12   |

Using only GROUP BY I get this:

SELECT name, surname, year, count(*) FROM Humans
GROUP BY name, surname, year

-----------------------------------------------
Table: Humans

 name | surname  | year  | count
------|----------|-------|-------
 Egor | Krid     |  14   | 1
 Egor | Petrenko |  12   | 1
 Dima | Maxnin   |  12   | 1
 Ivan | Krid     |  12   | 1

But this is the result I want to get:

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

Table: Humans

 name | surname  | year  | count
------|----------|-------|-------
 Dima |          |       | 1
 Egor |          |       | 2
 Ivan |          |       | 1
      | Krid     |       | 2
      | Maxnin   |       | 1
      | Petrenko |       | 1
      |          | 12    | 3
      |          | 14    | 1

As you can see, the columns are grouped into separate groups, and the last column shows the number of them. I assume that you will need to add this to the query: ORDER BY "name", "surname", "year". Can someone explain what to use for this in PostgreSQL?

>Solution :

This is what grouping sets() are for:

SELECT name, surname, year, count(*) 
FROM Humans
GROUP BY grouping sets( (name), (surname), (year) )

Online example

Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading