In SQL, query a table by transposing column results-CodePudding

Background

Forgive the title of this question, as I'm not really sure how to describe what I'm trying to do.

I have a SQL table, d, that looks like this:

 -- --- ------------ ------------ 
|id|sex|event_type_1|event_type_2|
 -- --- ------------ ------------ 
|a |m  |1           |1           |
|b |f  |0           |1           |
|c |f  |1           |0           |
|d |m  |0           |1           |
 -- --- ------------ ------------

The Problem

I'm trying to write a query that yields the following summary of counts of event_type_1 and event_type_2 cut (grouped?) by sex:

 ------------- ----- ----- 
|             |  m  |  f  |
 ------------- ----- ----- 
|event_type_1 |  1  |  1  |
 ------------- ----- ----- 
|event_type_2 |  2  |  1  |
 ------------- ----- -----

The thing is, this seems to involve some kind of transposition of the 2 event_type columns into rows of the query result that I'm not familiar with as a novice SQL user.

What I've tried

I've so far come up with the following query:

SELECT event_type_1, event_type_2, count(sex)
FROM d
group by event_type_1, event_type_2

But that only gives me this:

 ------------ ------------ ----- 
|event_type_1|event_type_2|count|
 ------------ ------------ ----- 
|1           |1           |1    |
|1           |0           |1    |
|0           |1           |2    |
 ------------ ------------ -----

CodePudding user response：

You can use a lateral join to unpivot the data. Then use conditional aggregate to calculate m and f:

select v.which,
       count(*) filter (where d.sex = 'm') as m,
       count(*) filter (where d.sex = 'f') as f
from d cross join lateral
     (values (d.event_type_1, 'event_type_1'),
             (d.event_type_2, 'event_type_2')
     ) v(val, which)
where v.val = 1
group by v.which;

Here is a db<>fiddle.