Suppress all 0 - 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 2005 Forums

Transact-SQL (2005)

Suppress all 0

Author

Topic

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 2009-08-11 : 11:05:25

I used code below to get data. It works fine.

SELECT OrderName
CASE WHEN [region] = 'region1' THEN SUM([Net Paid]) ELSE 0 END AS [Region1 Sum],
CASE WHEN [region] = 'region2' THEN SUM([Net Paid]) ELSE 0 END AS [Region2 Sum],
CASE WHEN [region] = 'region3' THEN SUM([Net Paid]) ELSE 0 END AS [Region3 Sum],
CASE WHEN [region] = 'region4' THEN SUM([Net Paid]) ELSE 0 END AS [Region4 Sum]
FROM Order
GROUP BY OrderName

How to modify code abode so that

if ([Region1 Sum] + [Region1 Sum]+[Region1 Sum]+[Region1 Sum]) = 0 then suppress it.
(do not display rows)

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-08-11 : 11:07:33

SELECT OrderName
CASE WHEN [region] = 'region1' THEN SUM([Net Paid]) ELSE 0 END AS [Region1 Sum],
CASE WHEN [region] = 'region2' THEN SUM([Net Paid]) ELSE 0 END AS [Region2 Sum],
CASE WHEN [region] = 'region3' THEN SUM([Net Paid]) ELSE 0 END AS [Region3 Sum],
CASE WHEN [region] = 'region4' THEN SUM([Net Paid]) ELSE 0 END AS [Region4 Sum]
FROM Order
GROUP BY OrderName
having not SUM([Net Paid]) = 0

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 2009-08-11 : 11:13:22

Sorry, I did not post right question
What I need is:

if ([Region1 Sum] + [Region2 Sum]+[Region3 Sum]+[Region4 Sum]) = 0
then suppress

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-08-11 : 11:16:30

That's what I thought I posted. If there are more regions then 1-4 then just add


SELECT OrderName, 
CASE WHEN [region] = 'region1' THEN SUM([Net Paid]) ELSE 0 END AS [Region1 Sum], 
CASE WHEN [region] = 'region2' THEN SUM([Net Paid]) ELSE 0 END AS [Region2 Sum],
CASE WHEN [region] = 'region3' THEN SUM([Net Paid]) ELSE 0 END AS [Region3 Sum],
CASE WHEN [region] = 'region4' THEN SUM([Net Paid]) ELSE 0 END AS [Region4 Sum] 
FROM [Order] a
where a.region in ('region1','region2','region3','region4')
GROUP BY OrderName
having not SUM([Net Paid]) = 0

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 2009-08-11 : 11:49:53

It works great! Thank you!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources