Complex SUM - 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

Complex SUM

Author

Topic

Laylow
Starting Member

14 Posts

Posted - 2011-02-02 : 09:35:46

Hi All,

I have a problem writing a query to sum 'AMOUNT' on distinct code e.g. CABE01P001 for all type e.g. 'ALP' and 'ARE'.

This is the script I have currently:
SELECT CODE,SUM (AMOUNT) AS AMOUNT,TYPE
FROM TABLE
WHERE (CODE) LIKE 'C_____P___'
GROUP BY CODE

Below is the Raw data and the Result I would like to see.

So for CABE01P001, ALP, I should have a total of 10924.95 as shown
in the result table below.

Regards

Laylow

RAW DATA
CODE______ TYPE AMOUNT
CABE01P001 ALP 5349.9
CABE01P001 ALP 5575.05
CABE01P001 ARE -5575.05
CABE01P001 ARE -5349.9
CABE01P002 ALP 1290
CABE01P002 ALP 4230
CABE01P002 ARE -4230
CABE01P002 ARE -1290
CABE01P003 ALP 170
CABE01P003 ALP 423
CABE01P003 ARE -423
CABE01P003 ARE -170

RESULT
CODE_____ TYPE AMOUNT
CABE01P001 ALP 10924.95
CABE01P001 ARE -10924.95
CABE01P002 ALP 5520
CABE01P002 ARE -5520
CABE01P003 ALP 593
CABE01P003 ARE -593

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-02-02 : 09:41:59

Add TYPE to the GROUP BY clause.

Laylow
Starting Member

14 Posts

Posted - 2011-02-02 : 09:55:11

quote:
Originally posted by robvolk

Add TYPE to the GROUP BY clause.

Hi Robvolk,

Slight problem with doing it that way is that I get figures which are wrong. I already tried that method and I was getting for example 16,500 for CABE01P001 when it should be 10,924.95

Would you know of a better approach?

Cheers

Laylow

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-02-02 : 10:13:59

The only way you'd get 16,500 is if you had additional rows for that CODE and TYPE combination. Can you add a COUNT(*) to your query and see if it shows more than 2 rows?

Laylow
Starting Member

14 Posts

Posted - 2011-02-02 : 10:24:43

quote:
Originally posted by robvolk

The only way you'd get 16,500 is if you had additional rows for that CODE and TYPE combination. Can you add a COUNT(*) to your query and see if it shows more than 2 rows?

Hi Robvolk,

Thanks for this, yes you are right there was a hidden row I didn't see.

Regards

Laylow

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-02-02 : 10:25:59

Those sneaky hidden rows!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources