is it possible? multi sums, one field, one table? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

is it possible? multi sums, one field, one table?

Author

Topic

SQLIsTheDevil
Posting Yak Master

177 Posts

Posted - 2009-05-12 : 17:05:55

Hi,

I'd like to perform multiple sums on one field, but each sum is of its own distinct entity. That is, I'd like to group the first sum by certain parameters and the second sum by a different set of parameters, all within the same table query.

I'd appreciate any help with this. Thank you!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-05-12 : 17:18:51

Just add the expressions you want to group on to the GROUP BY clause:

SELECT EntityID, sum(cost) FROM myTable GROUP BY EntityID

You can group on multiple columns as well as CASE expressions if you have more complicated logic. If you need further help you'll have to post details of your tables and queries.

SQLIsTheDevil
Posting Yak Master

177 Posts

Posted - 2009-05-12 : 17:33:49

FYI: I will be using this routine in a stored proc.

Thank you. Yes, it has crossed my mind to group by the entityid, as you put it. I'd prefer to spit out the results in one row, with each sum as a field, as opposed to one column, multiple rows. But I suppose there's no "clean" way to do what I'm asking (i.e. not involving joins and nested select clauses). I suppose I'll just have to loop through in the stored proc, somehow, if I want to use this method of retrieval.

Again, thank you for your help!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-05-12 : 18:44:29

As I said earlier, you'd have to post details on your tables and queries. You can have CASE expressions to perform certain logic:

declare @n table(n int)
insert @n values(1)
insert @n values(3)
insert @n values(5)
insert @n values(-20)
insert @n values(0)
insert @n values(0)
insert @n values(-16)

select sum(case when n>0 then n end) PositiveSum,
sum(case when n<0 then n end) NegativeSum,
sum(case when n>0 then 1 end) PositiveCount,
sum(case when n<0 then 1 end) NegativeCount,
sum(case when n=0 then 1 end) ZeroCount
from @n

SQLIsTheDevil
Posting Yak Master

177 Posts

Posted - 2009-05-13 : 08:52:09

Sure.

I have a table which looks something like this:



ID         Money        Type
1          5            USD
2          10           Pound
3          100          Euro
4          7            USD
5          10           Pound
6          50           Pound
7          80           Euro
8          50           Euro
...

And so forth.

What I need to do is add up the the monetary values, based on their type, but I don't wish to do 3 seperate statements to get it to print on one line. Would this case expression work in this scenario?

Thank you!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-05-13 : 11:21:36

SELECT Sum(CASE WHEN Type='USD' THEN [Money] END) USD,
Sum(CASE WHEN Type='Euro' THEN [Money] END) Euro,
Sum(CASE WHEN Type='Pound' THEN [Money] END) Pound
FROM myTable

SQLIsTheDevil
Posting Yak Master

177 Posts

Posted - 2009-05-14 : 16:28:27

Yup, you right. Worked like a charm! Thank you very much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources