Display Sum value based on Grouped datafield value

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 2008 Forums

Transact-SQL (2008)

Display Sum value based on Grouped datafield value

Author

Topic

BlackIce662
Starting Member

11 Posts

Posted - 2014-06-09 : 10:04:52

Good Day

This is my SQL Query. Everything after the 3rd line is required due to dependancies.

[CODE]
SELECT Customer.EMail, Amount=SUM(ABS((SELECT Top 1 Amount+VATAmount FROM Kitty WITH(NOLOCK)
Where Kitty.ResID=FreeWinners.ResID AND TransType='PZ')))
FROM FreeWinners
INNER JOIN Reservations WITH(NOLOCK) ON (Reservations.ResID = FreeWinners.ResID)
INNER JOIN Customer WITH(NOLOCK) ON (Reservations.EMail = Customer.EMail) WHERE FreeWinners.Comments_Approved!='No' AND FreeWinners.ResID
IN (SELECT ResID FROM Kitty WITH(NOLOCK) Where Kitty.ResID=FreeWinners.ResID AND TransType='PZ'
AND Year(TransDate)>1900 AND Approved='Yes') AND FreeWinners.ResID NOT IN (SELECT ResID FROM Kitty WITH(NOLOCK)
Where Kitty.ResID=FreeWinners.ResID AND TransType='PC')
GROUP BY Customer.EMail, FreeWinners.ResID
ORDER BY Amount DESC
[/CODE]
Some sample data

EMail Amount
bct@beyond.com 46269.75
matomel@ag.co.za 29700.00
yolan@jdo.co.za 26400.00
sgre@nics.co.za 25200.00
robgreish@hmail.com 22500.00
bct@beyond.com 20400.00
ghskhan@mail.com 17940.00
carstens@mail.com 16220.00
bishopk@sa.net 16100.00
bishopk@sa.net 16100.00

What I require is that the amounts for bct@beyond.com be added together
and then bishopk@sa.net values added together giving me the first and second records I require for
display. This will filter all through the data so if there are 5 amounts for bct@beyond.com I will
get a SUM returned for that email address based on the 5 amounts and then be sorted from highest to lowest.

So my output would then be

EMail Amount
bct@beyond.com 66669.75
bishopk@sa.net 32200.00
matomel@ag.co.za 29700.00

I realise I can't have Subquery for the SUM and I'm not sure if I'm using the Grouping correctly.

Any help will be greatly appreciated.

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2014-06-09 : 13:35:03

Try using a couple of cte's to do your sub queries

djj

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-09 : 13:43:45

[code]
WITH SomeCTE (Email, Amount)
AS
(
SELECT Customer.EMail, Amount=SUM(ABS((SELECT Top 1 Amount+VATAmount FROM Kitty WITH(NOLOCK)
Where Kitty.ResID=FreeWinners.ResID AND TransType='PZ')))
FROM FreeWinners
INNER JOIN Reservations WITH(NOLOCK) ON (Reservations.ResID = FreeWinners.ResID)
INNER JOIN Customer WITH(NOLOCK) ON (Reservations.EMail = Customer.EMail) WHERE FreeWinners.Comments_Approved!='No' AND FreeWinners.ResID
IN (SELECT ResID FROM Kitty WITH(NOLOCK) Where Kitty.ResID=FreeWinners.ResID AND TransType='PZ'
AND Year(TransDate)>1900 AND Approved='Yes') AND FreeWinners.ResID NOT IN (SELECT ResID FROM Kitty WITH(NOLOCK)
Where Kitty.ResID=FreeWinners.ResID AND TransType='PC')
GROUP BY Customer.EMail, FreeWinners.ResID
ORDER BY Amount DESC
)
SELECT Email, SUM(Amount) AS Amount
FROM SomeCTE
GROUP BY Email
ORDER BY Amount DESC
[/code]

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources