group by issue

[thompom]

Hi - have the following statement that almost displays what i need, except the fields countcurdealuse, countfwdealuse, countcurdealnew, countfwdealnew are not grouped on saletypelinkid - any ideas why?

SELECT STL.saletypelinkid,
SUM(case when TS.statusid=2 and 100*Year(dateadded)+Month(dateadded) = 100*Year(curDate()) +Month(curDate()) then 1 else 0 end) as countcurdealuse, 
SUM(case when TS.statusid=2 and 100*Year(dateadded)+Month(dateadded) > 100*Year(curDate()) +Month(curDate()) then 1 else 0 end) as countfwdealuse, 
SUM(case when TS.statusid=1 and 100*Year(dateadded)+Month(dateadded) = 100*Year(curDate()) +Month(curDate()) then 1 else 0 end) as countcurdealnew, 
SUM(case when TS.statusid=1 and 100*Year(dateadded)+Month(dateadded) > 100*Year(curDate()) +Month(curDate()) then 1 else 0 end) as countfwdealnew
FROM saletypelink AS STL  
LEFT JOIN deptlink AS TDP ON STL.deptlinkid = TDP.deptlinkid
LEFT JOIN event AS TD ON TD.deptlinkid = TDP.deptlinkid 
LEFT JOIN eventstocklink AS TS ON TS.eventid = TD.eventid
GROUP BY STL.saletypelinkid
ORDER BY STL.saletypelinkid

 

[r937]

i don't understand the question -- of course they are grouped on saletypelinkid, that's the only GROUP BY colulmn

what happens if you change all the LEFT OUTER JOINs to INNER JOINs?

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

 

[thompom]

hi rudy - ok you cant ignore the logic no matter what the results look like - anyways have simplified my query to

SELECT STL.saletypelinkid, stl.deptlinkid,
SUM(case when TS.statusid=1 and 100*Year(dateadded)+Month(dateadded) = 100*Year(curDate()) +Month(curDate()) then 1 else 0 end) as countcurdealnew
FROM saletypelink AS STL  
LEFT JOIN deptlink AS TDP ON STL.deptlinkid = TDP.deptlinkid
LEFT JOIN event AS TD ON TD.deptlinkid = TDP.deptlinkid 
LEFT JOIN eventstocklink AS TS ON TS.eventid = TD.eventid
GROUP BY STL.saletypelinkid
ORDER BY STL.saletypelinkid

at the moment countcurdealnew has the same result for each
stl.deptlinkid - i would like it to be summed for each STL.saletypelinkid

im using left joins because I want to show all STL.saletypelinkid wether they have results or not - using inner joins shows only the ones with results

 

[r937]

there is a problem here --

SELECT [blue]STL.saletypelinkid[/blue]
     , [red]stl.deptlinkid[/red]
     , SUM( ... ) AS countcurdealnew 
  FROM ...
GROUP 
    BY [blue]STL.saletypelinkid[/blue]

you say "at the moment countcurdealnew has the same result for each stl.deptlinkid - i would like it to be summed for each STL.saletypelinkid"

the obvious answer would be to remove stl.deptlinkid from the SELECT

what is the relationship between saletypelinkid and deptlinkid? one-to-one? many-to-one? one-to-many?

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

 

[thompom]

hi rudy,

got it sorted - i think!! - thanks for your reply - forced me to put pen to paper and think logically

SELECT STL.saletypelinkid,
SUM(case when TS.statusid=2 and 100*Year(dateadded)+Month(dateadded) = 100*Year(curDate()) +Month(curDate()) then 1 else 0 end) as countcurdealuse, 
SUM(case when TS.statusid=2 and 100*Year(dateadded)+Month(dateadded) > 100*Year(curDate()) +Month(curDate()) then 1 else 0 end) as countfwdealuse, 
SUM(case when TS.statusid=1 and 100*Year(dateadded)+Month(dateadded) = 100*Year(curDate()) +Month(curDate()) then 1 else 0 end) as countcurdealnew, 
SUM(case when TS.statusid=1 and 100*Year(dateadded)+Month(dateadded) > 100*Year(curDate()) +Month(curDate()) then 1 else 0 end) as countfwdealnew
FROM saletypelink AS STL 
LEFT JOIN event AS TD ON TD.saletypeid = STL.saletypelinkid  
LEFT JOIN eventstocklink AS TS ON TS.eventid = TD.eventid

 

[r937]

that won't work correctly without a GROUP BY clause

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

 

[thompom]

it is there [must have missed it on ctrl+c]!

...
GROUP BY STL.saletypelinkid
ORDER BY TL.siteid, STL.saletypelinkid

thx again