Case and total for multiple case statements

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)

Case and total for multiple case statements

Author

Topic

John Sourcer
Yak Posting Veteran

91 Posts

Posted - 2005-08-18 : 08:46:10

Hi Gurus,

I am struggling with the following problem:

SELECT SUM(CASE WHEN types.measureID = 1 then quantity*rate end),
SUM(CASE WHEN types.measureID = 2 then area*rate end),
SUM(CASE WHEN types.measureID = 3 then volume*rate end),
SUM(CASE WHEN types.measureID = 4 then perimeter*rate end),
SUM(CASE WHEN types.measureID = 5 then length*rate end)
FROM types INNER JOIN rates ON types.typeID = rates.typeID INNER JOIN
estimate ON types.typeID = estimate.typeID WHERE types.prjID = 1

This works and gives me the totals for each type depending on their measurement ID. How can I get all of these values as a single result? i.e: 83462986.87 Not as 5 columns!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-08-18 : 08:53:11

maybe:
SUM(quantity + area + volume + perimeter + length)*rate

Go with the flow & have fun! Else fight the flow

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-18 : 09:06:13

and make sure they are all not nulls

Madhivanan

Failing to plan is Planning to fail

John Sourcer
Yak Posting Veteran

91 Posts

Posted - 2005-08-18 : 09:24:40

Hi Guys,

Thanks for the prompt reply.

How will the following work? It doesn't account for measure ID and each row has values for these columns anyway!

SUM(quantity + area + volume + perimeter + length)*rate

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-18 : 09:38:11

Did you use like this?

Select SUM(quantity + area + volume + perimeter + length)*rate
FROM types INNER JOIN rates ON types.typeID = rates.typeID INNER JOIN
estimate ON types.typeID = estimate.typeID WHERE types.prjID = 1

Madhivanan

Failing to plan is Planning to fail

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-08-18 : 09:42:23

SUM(CASE WHEN types.measureID in (1,2,3,4,5) then SUM(quantity + area + volume + perimeter + length)*rate else 0 end)

Go with the flow & have fun! Else fight the flow

John Sourcer
Yak Posting Veteran

91 Posts

Posted - 2005-08-18 : 09:43:40

madhivanan,

What about the measureID which tells me which column to multiply my rate by? If you view my SQL statement again you will notice that the varying measureID results in a different column being selected.

Thanks for you interest!

John Sourcer
Yak Posting Veteran

91 Posts

Posted - 2005-08-18 : 09:47:44

Thanks spirit1,

But that won't work either! Am I missing something? My query works but results in 5 columns one for each measurement type i.e qty, area, volume, etc. Only one of the measurement types are applied per row.

Instead of 5 columns I would like just one total value for the columns!

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-08-18 : 10:04:19

Convert the query to produce 1 row per measure....and then sum the rows.

John Sourcer
Yak Posting Veteran

91 Posts

Posted - 2005-08-18 : 10:09:46

Thanks Andrew,

Any pointers? Is there no way to bind the results of all the columns into one column?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-18 : 10:10:58

Or

Select sum(col1+col2+col3+col4+clo5) from (
SELECT SUM(CASE WHEN types.measureID = 1 then quantity*rate end) as col1,
SUM(CASE WHEN types.measureID = 2 then area*rate end) as col2,
SUM(CASE WHEN types.measureID = 3 then volume*rate end) as col3,
SUM(CASE WHEN types.measureID = 4 then perimeter*rate end) as col4,
SUM(CASE WHEN types.measureID = 5 then length*rate end) as col5
FROM types INNER JOIN rates ON types.typeID = rates.typeID INNER JOIN
estimate ON types.typeID = estimate.typeID WHERE types.prjID = 1
) T

Madhivanan

Failing to plan is Planning to fail

John Sourcer
Yak Posting Veteran

91 Posts

Posted - 2005-08-18 : 10:34:41

Thanks madhivanan,

That works great! Just need to test for nulls now!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources