addition of fields 1

[thompom]

Hi,
Need [TOTAL] to add derived fields as per below, but cant get it to work - any ideas welcome

SELECT IF(ISNULL(newcount), 0, newcount) AS newcount, 
IF(ISNULL(usedcount), 0, usedcount) AS usedcount,
(newcount + usedcount) AS TOTAL 

FROM users 
LEFT JOIN (SELECT count(action.actionid) AS newcount, action.userid FROM action INNER JOIN deal ON deal.actionid = action.actionid INNER JOIN eventstocklink ON eventstocklink.eventstocklinkid = action.eventstocklinkid WHERE (eventstocklink.statusid = 1 OR eventstocklink.statusid = 3 AND action.typeid = 2) AND date(action.actiondate) BETWEEN '20090801' AND '20090831' GROUP BY action.userid) AS newcount ON newcount.userid = users.userid 

LEFT JOIN (SELECT count(action.actionid) AS usedcount, action.userid FROM action INNER JOIN deal ON deal.actionid = action.actionid INNER JOIN eventstocklink ON eventstocklink.eventstocklinkid = action.eventstocklinkid WHERE (eventstocklink.statusid = 2 AND action.typeid = 2) AND date(action.actiondate) BETWEEN '20090801' AND '20090831' GROUP BY action.userid) AS usedcount ON usedcount.userid = users.userid

 

[r937]

here's your query, rewritten for readability by humans, and with a couple minor changes at the beginning ...

SELECT users.userid
     , COALESCE(n.newcount,0) AS newcount
     , COALESCE(u.usedcount,0) AS usedcount
     , COALESCE(n.newcount,0) +
       COALESCE(u.usedcount,0) AS TOTAL 
  FROM users 
LEFT 
  JOIN ( SELECT count(action.actionid) AS newcount
              , action.userid 
           FROM action 
         INNER 
           JOIN deal 
             ON deal.actionid = action.actionid 
         INNER 
           JOIN eventstocklink 
             ON eventstocklink.eventstocklinkid 
              = action.eventstocklinkid 
          WHERE [red]( eventstocklink.statusid = 1 
               OR eventstocklink.statusid = 3 
              AND action.typeid = 2 ) [/red]
            AND date(action.actiondate) BETWEEN '20090801' 
                                            AND '20090831' 
         GROUP 
             BY action.userid ) AS n 
    ON n.userid = users.userid 
LEFT 
  JOIN ( SELECT count(action.actionid) AS usedcount
              , action.userid 
           FROM action 
         INNER 
           JOIN deal 
             ON deal.actionid = action.actionid 
         INNER 
           JOIN eventstocklink 
             ON eventstocklink.eventstocklinkid 
              = action.eventstocklinkid 
          WHERE ( eventstocklink.statusid = 2 
              AND action.typeid = 2 ) 
            AND date(action.actiondate) BETWEEN '20090801' 
                                            AND '20090831' 
         GROUP 
             BY action.userid ) AS u 
    ON u.userid = users.userid

if you're not getting the right results, it might be because the part in red should really look like this --

          WHERE [blue]( eventstocklink.statusid IN ( 1 , 3 ) 
              AND action.typeid = 2 ) [/blue]

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

 

[thompom]

thanks rudy - never heard of COALESCE before, this norwegian lady explains more:

http://lailablog.blogspot.com/2005/07/mysql-coalesce-example.html

ps. will format my posts better in future ;-)