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:

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?

CodePudding user response：

This is what grouping sets() are for:

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

Online example

CodePudding user response：

Try this (Fiddle)

select name,'' as surname, count(*)
from humans
group by name

union

select '',surname, count(*)
from humans
group by surname
order by 2,1;