query help 1

[thompom]

Hi,
Need help with following query

SELECT 
IF((TS.statusid=2), (count(TA.actionid)), 0) AS usetotal,
IF((TS.statusid=1 OR TS.statusid =3), (count(TA.actionid)), 0) AS newtotal,
R.date
FROM event AS TD 
LEFT JOIN action AS TA ON TA.eventid = TD.eventid 
LEFT JOIN eventstocklink AS TS ON TS.eventstocklinkid = TA.eventstocklinkid 
LEFT JOIN stockstate AS TST ON TST.stateid = TS.statusid 
LEFT JOIN response AS R ON R.actionid = TA.actionid 
LEFT JOIN saletypelink AS STL ON STL.saletypelinkid = TD.saletypeid
LEFT JOIN saletype AS ST ON ST.saletypeid = STL.saletypeid
WHERE R.reasonid = 'Handover' AND R.date BETWEEN '20090921' AND '20090927' AND TD.siteid = 1
GROUP BY date(r.date)
ORDER BY date(r.date)

this query needs to count new/used totals, grouped by date - however the query above counts
all items under usedtotal - unless there are no used items, in which case the correct total is shown under newtotal

hope this makes sense - let me if not

 

[SimonSellick]

Hi,

I'm new to MySQL so this could be a little off-beam, but I would have thought that you needed to group after splitting the statusid values:

select
  sum(case when ts.statusid = 2 then 1 else 0 end) newtotal
, sum(case when ts.statusid in (1, 3) then 1 else 0 end) usedtotal
...

HTH
Simon

 

[thompom]

simon heres a * - thanks v much