CODEWITHSUNDEEP
mysqlsql
Mik
Mik

Reputation: 105

COUNT for every GROUP BY with non existent values

I have the following 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 i have 8 different numbers I want to count this eight different eid , even counted as "0" what i want to get is an array contain 8 values and the keys should be the eight different names. "vs" is 3 different numbers every time i count i want for this on "rid" = %d and "vs" = %d ( specific rid and specific vs) 

SELECT count(*) as count FROM notification 
WHERE rid = 60 AND vs = 2 AND isN = 1 GROUP BY eId 

rid=>60,21=>6,22=>3,vs=>2,isN=>1

(this is what i get with the 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 is what i want. eight counted, of course this numbers counted not existed on eId so i want to return as a zero)

Upvotes: 0

Views: 542

Answers (3)

spencer7593
spencer7593

Reputation: 108370

Here's one way to get the specified resultset:

 SELECT d.rid           AS `rid`
      , SUM(n.eid<=>21) AS `21`
      , SUM(n.eid<=>22) AS `22`
      , SUM(n.eid<=>23) AS `23`
      , SUM(n.eid<=>33) AS `33`
      , SUM(n.eid<=>34) AS `34`
      , SUM(n.eid<=>35) AS `35`
      , SUM(n.eid<=>36) AS `36`
      , SUM(n.eid<=>41) AS `41`
      , SUM(n.eid<=>42) AS `42`
      , d.vs            AS `vs`
      , d.isN           AS `isN`
   FROM ( SELECT %d AS rid, %d AS vs, 1 AS isN ) d
   LEFT
   JOIN notification n
     ON n.rid = d.rid
    AND n.vs  = d.vs
    AND n.isN = d.isN
  GROUP
     BY d.rid
      , d.vs
      , d.isN

Note: the expression (n.eid<=>21) is shorthand for IF(n.eid=21,1,0), or the more ANSI-standard CASE WHEN n.eid = 21 THEN 1 ELSE 0 END. That gives a 0 or a 1, which can then be aggregated with a SUM function.

You could get equivalent results using any of these forms:

     , SUM(n.eid<=>21)                             AS `21`
     , COUNT(IF(n.eid=22,1,NULL))                  AS `22`
     , SUM(IF(n.eid=23,1,0))                       AS `23`
     , COUNT(CASE WHEN n.eid = 33 THEN 1 END)      AS `33`
     , SUM(CASE WHEN n.eid = 34 THEN 1 ELSE 0 END) AS `34`

The "trick" we are using here is that we are guaranteed that the inline view aliased as d will return one row. Then we are using a LEFT JOIN operator to pick up all "matching" rows from the notification table. The GROUP BY is going to force all those rows to be collapsed (aggregated) back down to a single row. And we are using a conditional test on each row to see if it is to be included in a given count or not, the "trick" is to return a 0 or a 1, for each row, and then add up all the 0s and 1s to get a count.

NOTE: If you use a COUNT(expr) aggregate, you want that expr to return a non-NULL when the row is to be included in the count, and a NULL when the row is not to be included in the count.

If you use a SUM(expr), then you want expr to return a 1 when the row is to be included in the count, and return a 0 when it's not. (We want a 0 rather than a NULL so that we will be guaranteed that SUM(expr) will return a "zero count" (i.e a 0 rather than a NULL) when there are no rows to be included. (Of course, we could use an IFNULL function to replace a NULL with a 0, but in this case it's simple enough to avoid the need for that.)

Note that one advantage of this approach to "counting" is that it can easily extended to get "combined" counts, or to include a row in several different counts. e.g.

    , SUM(IF(n.eid IN (41,42),1,0)) AS `total_41_and_42`

would get us a total count of eid=41 and eid=42 rows. (That's not such a great example, because we could just as easily calculate that on the client side by adding the two counts together. But that really becomes an advantage if you were doing more elaborate counts, and wanted to count a single row in multiple columns ...

   , SUM(IF(n.eid=42,1,0))            AS eid_42
   , SUM(IF(n.eid=42 AND foo=1,1,0)   AS eid_42_foo_1
   , SUM(IF(n.eid=42 AND foo=2,1,0))  AS eid_42_foo_2

We can get all those separate counts with just "one pass" through notification table. If we tried to do those checks in the WHERE clause, we'd likely need multiple passes through the table.

Upvotes: 1

Strawberry
Strawberry

Reputation: 33935

So, essentially what you're looking for is this?...

 SELECT rid,eid,vs, COUNT(*) FROM notification GROUP BY rid,eid,vs;
 +-----+-----+----+----------+
 | rid | eid | vs | COUNT(*) |
 +-----+-----+----+----------+
 |   0 |  22 |  2 |        2 |
 |   3 |  21 |  2 |        1 |
 |   3 |  22 |  2 |        2 |
 |  60 |  21 |  2 |        6 |
 |  60 |  22 |  2 |        3 |
 |  81 |  21 |  2 |        1 |
 |  81 |  22 |  2 |        3 |
 +-----+-----+----+----------+
 7 rows in set (0.11 sec)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269463

What you need is a driver table that has all the values you want to output. You can then left outer join this to the actual data:

SELECT count(notification.eid) as count
FROM (select distinct eid
      from notification
     ) drivers left outer join
     (select *
      from notification
      WHERE rid = %d AND vs = %d AND isN = 1
     ) n
     on driver.eid = notification.eid
GROUP BY driver.eId

You should also include the eid in the select clause, unless you are depending on the final ordering of the output (MySQL, unlike any other database, does guarantee the ordering of results after a group by.)

Upvotes: 1

Related Questions