Case count query 1

[thompom]

Hi,

Need help with the following query - if there is a count for USED then no NEW are counted for each day, if there are no used then NEW is counted - can anyone tell me why?

SELECT 
CASE eventstocklink.statusid WHEN 2 THEN count(CB.actionid) ELSE 0 END AS USED, 
CASE eventstocklink.statusid WHEN 1 THEN count(CB.actionid) ELSE 0 END AS NEW
FROM action AS CB 
LEFT JOIN event ON event.eventid = CB.eventid 
LEFT JOIN eventstocklink ON eventstocklink.eventstocklinkid = CB.eventstocklinkid 
WHERE date(CB.actiondate) 
BETWEEN '20091102' AND '20091108' 
GROUP BY eventstocklink.statusid, date(CB.actiondate)

thanks in advance

 

[r937]

your CASE belongs inside the COUNT, not the other way around

SELECT eventstocklink.statusid
     , DATE(CB.actiondate)
     , COUNT(CASE WHEN eventstocklink.statusid = 2 
                  THEN CB.actionid END) AS USED
     , COUNT(CASE WHEN eventstocklink.statusid = 1 
                  THEN CB.actionid END) AS NEW
  FROM action AS CB 
LEFT OUTER
  JOIN event 
    ON event.eventid = CB.eventid 
LEFT OUTER
  JOIN eventstocklink 
    ON eventstocklink.eventstocklinkid = CB.eventstocklinkid 
 WHERE CB.actiondate >= '2009-11-02' 
   AND CB.actiondate  < '2009-11-09' 
GROUP 
    BY eventstocklink.statusid
     , DATE(CB.actiondate)

i've made a couple of other suggested improvements in your query, most importantly in the WHERE clause to enable the optimizer to use an index on the actiondate column

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[thompom]

thanks v.much rudy