Sum or Group By function? - 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

General SQL Server Forums

New to SQL Server Programming

Sum or Group By function?

Author

Topic

MevaD
Starting Member

32 Posts

Posted - 2008-12-04 : 10:54:52

Hi,

I have table of accounts and account balances. I need to select certain rows and sum the balance of the selected accounts.

Example:

ACCOUNT Table
-----------------
AcctID | Balance
-----------------
10 | 100.00
11 | 60.00
26 | 300.00
54 | 5.00

I would like to be able to select certain groups of accounts and sum the balance of the accounts:

For example:

AcctID 10 Balance + AcctID 26 Balance AS Total_1
AcctID 26 Balance + AcctID 54 Balance AS Total_2

Then, how can I show the results like this:

RESULTS
------------------
Column1 | Column2
------------------
Total_1 | 400.00
Total_2 | 305.00

Is this possible?

Thanks for any help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-04 : 11:02:41

[code]
SELECT SUM(CASE WHEN AcctID=10 OR AcctID=26 THEN Balance ELSE 0 END ) AS Total_1,
SUM(CASE WHEN AcctID=26 OR AcctID=54 THEN Balance ELSE 0 END ) AS Total_2
FROM Table
[/code]

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-12-04 : 11:02:48

[code]select case when acctid in (10,26) then 'Total_1'
when acctid in (26,54) then 'Total_2' end as Column1,
sum(balance) as Column2
from AcctTab
group by
case when acctid in (10,26) then 'Total_1'
when acctid in (26,54) then 'Total_2' end[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-04 : 11:05:26

Yes. First of all you must create an auxiliary table where you "group" accounts together.
Such as

DECLARE	@Grps TABLE
	(
		GrpNum INT,
		AcctID INT
	)

Then you easily can join that table to your first table and get this result

GrpNum	Total
1	400.00
2	305.00

with this code

DECLARE	@Sample TABLE
	(
		AcctID INT,
		Balance MONEY
	)

INSERT	@Sample
SELECT	10, 100.00 UNION ALL
SELECT	11,  60.00 UNION ALL
SELECT	26, 300.00 UNION ALL
SELECT	54,   5.00

DECLARE	@Grps TABLE
	(
		GrpNum INT,
		AcctID INT
	)

INSERT	@Grps
SELECT	1, 10 UNION ALL
SELECT	1, 26 UNION ALL
SELECT	2, 26 UNION ALL
SELECT	2, 54

SELECT		g.GrpNum,
		SUM(s.Balance) AS Total
FROM		@Grps AS g
INNER JOIN	@Sample AS s ON s.AcctID = g.AcctID
GROUP BY	g.GrpNum

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

MevaD
Starting Member

32 Posts

Posted - 2008-12-04 : 11:18:22

Thanks everyone. You always make it seem so easy.

Thanks again!

MevaD
Starting Member

32 Posts

Posted - 2008-12-04 : 11:26:54

hanbingl,

I'm getting an additional row in my results with NULL value in column1 and the SUM of all values in column2.

Is there a way to alter your query to only show the totals of the columns I have selected?

From my example above:

RESULTS
------------------
Column1 | Column2
------------------
Total_1 | 400.00
NULL | 705.00 <-- (Can I get rid of this?)
Total_2 | 305.00

Thanks!

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-12-04 : 11:32:17

sorry, MevaD, I did not read your question correctly. The solution I posted was wrong. I think Peso has the most correct answer.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-04 : 11:37:41

[code]
select case when acctid in (10,26) then 'Total_1'
when acctid in (26,54) then 'Total_2' end as Column1,
sum(balance) as Column2
from AcctTab
where acctid in (10,26,54)
group by
case when acctid in (10,26) then 'Total_1'
when acctid in (26,54) then 'Total_2' end
[/code]

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-12-04 : 11:39:07

[code]SELECT
'Total_1' AS Col1
SUM(CASE WHEN AcctID=10 OR AcctID=26 THEN Balance ELSE 0 END ) AS Col2,

FROM
Table

UNION ALL

SELECT
'Total_2'
SUM(CASE WHEN AcctID=26 OR AcctID=54 THEN Balance ELSE 0 END )
FROM
Table
[/code]

MevaD
Starting Member

32 Posts

Posted - 2008-12-04 : 11:41:53

No problem, hanbingl thanks for the reply. I'm working on Peso's solution now.

MevaD
Starting Member

32 Posts

Posted - 2008-12-04 : 11:52:33

Thanks visakh16 - that works great.

Thanks Peso - your solution will make administration of the application very clean and easy.

Thanks again everyone.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources