CODEWITHSUNDEEP
sqlsqlite
jocke
jocke

Reputation: 11

sqlite get total rows from group by

My table contains this data.

c1 c2
A No
A No
A Yes
A Yes
A Yes
B No
B No
B No
B Yes 
SELECT c1,c2,count(*) Nr FROM db GROUP BY c1,c2 ORDER BY c1,c2;

Give c1,c2,Nr columns How to add a total column to output that give sum of Yes/No for A and B?

c1 c2 Nr Total
A Yes 3 5
A No 2 5
B Yes 1 4
B No 3 4

Upvotes: 0

Views: 91

Answers (3)

forpas
forpas

Reputation: 164214

You can use COUNT() window function twice, without GROUP BY:

SELECT DISTINCT c1, c2, 
       COUNT(*) OVER (PARTITION BY c1, c2) Nr,
       COUNT(*) OVER (PARTITION BY c1) Total
FROM db
ORDER BY c1, c2;

See the demo.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271141

You can use window functions:

SELECT c1, c2, count(*) as Nr,
       SUM(COUNT(*)) OVER (PARTITION BY c1) as Total
FROM db
GROUP BY c1, c2
ORDER BY c1, c2;

Upvotes: 1

d0little
d0little

Reputation: 476

This should achieve your desired output:

SELECT db.c1, db.c2, count(*) Nr, db2.total
FROM db
LEFT JOIN (SELECT c1, count(*) total FROM db GROUP BY c1) db2
ON db.c1 = db2.c1
GROUP BY db.c1, db.c2 
ORDER BY db.c1, db.c2;

Upvotes: 0

Related Questions