SQLITE PIVOT TABLE WITH COUNT

Question

Can you help me plase ? I want a result like the table below with Sqlite

.	task_name	status_code	department
1	A	START	PUR
2	B	START	ENG
3	C	FINISH	ENG
4	D	NOT ACTIVE	DES
5	E	START	DES
6	F	NOT ACTIVE	ENG

.	START	FINISH	NOT ACTIVE	TOTAL
PUR	1			1
ENG	1	1	1	3
DES	1		1	2

Answer 1

You can use conditional aggregation such as

SELECT department,
       SUM(CASE WHEN status_code = 'START' THEN 1 END) AS "START",
       SUM(CASE WHEN status_code = 'FINISH' THEN 1 END) AS "FINISH",
       SUM(CASE WHEN status_code = 'NOT ACTIVE' THEN 1 END) AS "NOT ACTIVE",
       COUNT(*) AS total
  FROM t
 GROUP BY department

Demo