mysql - COUNT for every GROUP BY with non existent values -

mysql - COUNT for every GROUP BY with non existent values -

i have next table

id rid eid vs isn 24 3 22 2 1 25 3 21 2 1 26 60 21 2 1 27 60 21 2 1 28 60 21 2 1 29 60 21 2 1 30 60 21 2 1 31 60 21 2 1 32 81 21 2 1 35 60 22 2 1 36 81 22 2 1 37 0 22 2 1 38 60 22 2 1 39 81 22 2 1 40 0 22 2 1 41 60 22 2 1 42 81 22 2 1 43 3 22 2 1

on eid have 8 different numbers want count 8 different eid , counted "0" want array contain 8 values , keys should 8 different names. "vs" 3 different numbers every time count want on "rid" = %d , "vs" = %d ( specific rid , specific vs)

select count(*) count notification rid = 60 , vs = 2 , isn = 1 grouping eid rid=>60,21=>6,22=>3,vs=>2,isn=>1

(this above)

rid=>60,21=>6,22=>3,23=>0,33=>0,34=>0,35=>0,36=>0,41=>0,42=>0,vs=>2,isn=>1

(this want. 8 counted, of course of study numbers counted not existed on eid want homecoming zero)

here's 1 way specified resultset:

select d.rid `rid` , sum(n.eid<=>21) `21` , sum(n.eid<=>22) `22` , sum(n.eid<=>23) `23` , sum(n.eid<=>33) `33` , sum(n.eid<=>34) `34` , sum(n.eid<=>35) `35` , sum(n.eid<=>36) `36` , sum(n.eid<=>41) `41` , sum(n.eid<=>42) `42` , d.vs `vs` , d.isn `isn` ( select %d rid, %d vs, 1 isn ) d left bring together notification n on n.rid = d.rid , n.vs = d.vs , n.isn = d.isn grouping d.rid , d.vs , d.isn

note: look (n.eid<=>21) shorthand if(n.eid=21,1,0), or more ansi-standard case when n.eid = 21 1 else 0 end. gives 0 or 1, can aggregated sum function.

you equivalent results using of these forms:

, sum(n.eid<=>21) `21` , count(if(n.eid=22,1,null)) `22` , sum(if(n.eid=23,1,0)) `23` , count(case when n.eid = 33 1 end) `33` , sum(case when n.eid = 34 1 else 0 end) `34`

the "trick" using here guaranteed inline view aliased d homecoming 1 row. using left bring together operator pick "matching" rows notification table. group by going forcefulness rows collapsed (aggregated) downwards single row. , using conditional test on each row see if included in given count or not, "trick" homecoming 0 or 1, each row, , add together 0s , 1s count.

note: if utilize count(expr) aggregate, want expr homecoming non-null when row included in count, , null when row not included in count.

if utilize sum(expr), want expr homecoming 1 when row included in count, , homecoming 0 when it's not. (we want 0 rather null guaranteed sum(expr) homecoming "zero count" (i.e 0 rather null) when there no rows included. (of course, utilize ifnull function replace null 0, in case it's simple plenty avoid need that.)

note 1 advantage of approach "counting" can extended "combined" counts, or include row in several different counts. e.g.

, sum(if(n.eid in (41,42),1,0)) `total_41_and_42`

would total count of eid=41 , eid=42 rows. (that's not such great example, because calculate on client side adding 2 counts together. becomes advantage if doing more elaborate counts, , wanted count single row in multiple columns ...

, sum(if(n.eid=42,1,0)) eid_42 , sum(if(n.eid=42 , foo=1,1,0) eid_42_foo_1 , sum(if(n.eid=42 , foo=2,1,0)) eid_42_foo_2

we can separate counts "one pass" through notification table. if tried checks in clause, we'd need multiple passes through table.

mysql sql pdo mysqli