SUM question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SUM question

Author

Topic

misterzr
Starting Member

49 Posts

Posted - 2006-02-03 : 09:58:58

I am trying to find out if the sum of one column(with conditions) is greater than another column.

Here is the code that won't work but is what I am trying to say

SELECT IMLITM,IMITM,LIPQOH AS ONHAND,SUM(LIHCOM) AS COMMITS,IMDSC1,IMSTKT,IMLNTY
FROM PRODDTA.F4101 INNER JOIN PRODDTA.F41021 ON IMITM = LIITM
GROUP BY IMLITM,IMITM,LIPQOH,LIHCOM,IMDSC1,IMSTKT,IMLNTY
HAVING (SUM(LIHCOM) WHERE LIPBIN ='S') > (LIPQOH WHERE LIPBIN ='P') 
AND LIPQOH > 0
ORDER BY IMLITM

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-02-03 : 10:14:16

I'm not clear about ur requirement. But I think u can make use of Case -- When --- then Instead of where clause!!

U can learn about Case from Books On Line.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-02-03 : 11:03:13

[code]having
SUM(case when LIPBIN ='S' then LIHCOM else 0 end) >
SUM(case when LIPBIN ='P' then LIPQOH else 0 end)[/code]

CODO ERGO SUM

misterzr
Starting Member

49 Posts

Posted - 2006-02-03 : 12:32:01

This is what I have so far and it will run but it is returning some values that still have LIQOH greater than sum of LIHCOM.

Any suggestions?

SELECT DISTINCT IMLITM,IMITM,SUM(case when LIPBIN ='S' then LIHCOM else 0 end) AS COMMITS,IMDSC1
FROM PRODDTA.F4101 INNER JOIN PRODDTA.F41021 ON IMITM = LIITM
GROUP BY IMLITM,IMITM,LIPQOH,LIHCOM,IMDSC1,IMSTKT,IMLNTY,LIPBIN,LILOCN,LIMCU
having (SUM(case when LIPBIN ='S'  then LIHCOM ELSE 0 END) > SUM(case when LIPBIN ='P' then LIPQOH ELSE 0 END ))
AND LIPQOH > '0'
AND LILOCN <> 'CLR10000'
AND LILOCN NOT LIKE 'CFO%'
ORDER BY IMLITM

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2006-02-03 : 12:55:04

Why are you summing things that appear in the GROUP BY clause?

misterzr
Starting Member

49 Posts

Posted - 2006-02-03 : 13:37:14

I thought I needed them in the group by because it won't run with them there.

misterzr
Starting Member

49 Posts

Posted - 2006-02-03 : 13:46:46

Sorry I meant to say WITHOUT them in the group by clause

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2006-02-03 : 14:08:05

But that's mainly because you've put the scalar conditions LIPQOH > '0' AND LILOCN <> 'CLR10000' AND LILOCN NOT LIKE 'CFO%' in the HAVING clause, which is virtually never what you want. Create a WHERE clause and move them there, then you can stop grouping by things you don't want to group by!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources