In SQLite, transpose query results to count values-CodePudding

Background

I have a SQLite 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  |
 ------------- ----- -----

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    |
 ------------ ------------ -----

In another similar post, I asked how to do this in PostgreSQL, and was given an answer by Gordon Linoff that worked:

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_2, 'event_type_2'),
             (d.event_type_1, 'event_type_1')
     ) v(val, which)
where v.val = 1
group by v.which", drv = "SQLite")

My needs have changed, though, and I need to do this in SQLite. I've been trying to adapt this code to SQLite using lateral joins, but haven't had any luck.

CodePudding user response：

SELECT 'event_type_1' AS event_type, SUM(sex='m') AS m, SUM(sex='f') AS f FROM d WHERE event_type_1
UNION
SELECT 'event_type_2' AS event_type, SUM(sex='m') AS m, SUM(sex='f') AS f FROM d WHERE event_type_2

Or, if you can transpose the rows/columns in your calling programming language instead of doing it in SQL, you can just use:

SELECT sex, SUM(event_type_1), SUM(event_type_2) FROM d GROUP BY sex

CodePudding user response：

In SQLite there are various methods. One uses a cross join:

select which,
       sum(sex = 'm') as m,
       sum(sex = 'f') as f
from d cross join
     (select 'event_type_1' as which union all select 'event_type_2' as which) v
group by which;

The advantage of using CROSS JOIN is that the original table is scanned only once.