help with a query - 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

Transact-SQL (2000)

help with a query

Author

Topic

squarefish
Starting Member

28 Posts

Posted - 2008-11-27 : 07:46:58

Hi there,

I have the following table of data.

type | FP | IP | WP | Code
---------------------------------
retail | 100 | 200 | 0 | CUC
retail | 1000| 0 | 200 | CUC
used | 100 | 200 | 400 | ASC
retail | 0 | 200 | 400 | CUC
used | 100 | 200 | 0 | CUC

What I would like to see is the following

type | FP | IP | WP | Code
---------------------------------
retail | 2 | 2 | 2 | CUC
used | 0 | 1 | 1 | CUC
used | 1 | 1 | 1 | ASC

So basically if there is a value greater than 0 in FP, IP, and WP it should add to a count. It should be grouped by code and type.

I can't figure out if this is even possible in one go or not?

Help

Richard

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-27 : 07:52:31

[code]SELECT type,
SUM(SIGN(FP)) AS FP,
SUM(SIGN(IP)) AS IP,
SUM(SIGN(WP)) AS WP,
code
FROM Table1
GROUP BY type,
code[/code]

E 12°55'05.63"
N 56°04'39.26"