Help with SUM/GROUP BY issue

Question

Post reply

Help with SUM/GROUP BY issue

scienceof8

Old Hand

Points: 349

February 7, 2020 at 3:22 pm

#3723964

Hopefully somebody can help me with what i was hoping to be simple.

I have a simple dataset/table:

sql1

Create the table with the following:

DROP TABLE IF EXISTS dbo.TestTable1;


CREATE TABLE dbo.TestTable1
(
    perVARCHAR(5) NOT NULL,
    companyVARCHAR(5) NOT NULL,
accountVARCHAR(5) NOT NULL,
amountINT
);


INSERT INTO dbo.TestTable1(per, company, account, amount) VALUES('P1', 'Comp1', 'Acc1', 5);
INSERT INTO dbo.TestTable1(per, company, account, amount) VALUES('P2', 'Comp1', 'Acc2', 10);
INSERT INTO dbo.TestTable1(per, company, account, amount) VALUES('P2', 'Comp1', 'Acc3', 15);
INSERT INTO dbo.TestTable1(per, company, account, amount) VALUES('P2', 'Comp1', 'Acc1', 5);
INSERT INTO dbo.TestTable1(per, company, account, amount) VALUES('P1', 'Comp1', 'Acc1', 3);
INSERT INTO dbo.TestTable1(per, company, account, amount) VALUES('P1', 'Comp2', 'Acc2', 5);
INSERT INTO dbo.TestTable1(per, company, account, amount) VALUES('P2', 'Comp1', 'Acc3', 2);
INSERT INTO dbo.TestTable1(per, company, account, amount) VALUES('P2', 'Comp2', 'Acc4', 5);
INSERT INTO dbo.TestTable1(per, company, account, amount) VALUES('P1', 'Comp1', 'Acc1', 10);
INSERT INTO dbo.TestTable1(per, company, account, amount) VALUES('P1', 'Comp1', 'Acc5', 5);
INSERT INTO dbo.TestTable1(per, company, account, amount) VALUES('P2', 'Comp2', 'Acc1', 10);
INSERT INTO dbo.TestTable1(per, company, account, amount) VALUES('P1', 'Comp2', 'Acc3', 20);
INSERT INTO dbo.TestTable1(per, company, account, amount) VALUES('P1', 'Comp2', 'Acc1', 5);
INSERT INTO dbo.TestTable1(per, company, account, amount) VALUES('P1', 'Comp1', 'Acc2', 80);

I am looking to 'group by','sum' and 'count' certain columns in the result set.

I have got this far:

sql2

...using the following:

SELECT
per
,company
,account
,COUNT(*) AS 'accNum'
,SUM(amount) as 'accTotals'

FROM
TestTable1

WHERE
per = 'P1'

GROUP BY per,company,account
ORDER BY company ASC, account ASC

But what i am looking for is, for each company, sum all account values, and present the result in an additional column. I will filter on a specific period. (in this case: 'P1')For example:

sql3

How can i achieve the above?

This is a small part of a bigger script that will combine and compare/reconcile multiple periods (per).

This topic was modified 4 years, 7 months ago by scienceof8.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

This should do it for the extra column:

SUM (accTotal) OVER (PARTITION BY per, company) AS Total

John

Edit - actually, that won't work straight out the box. You will either need to change the whole query to use SUM() OVER, or put your query as it is into a CTE and add the line above to the final result set.

SELECT DISTINCT
per
,company
,account
,COUNT(*) OVER (PARTITION BY per, company, account) AS 'accNum'
,SUM(amount) OVER (PARTITION BY per, company, account) as 'accTotals'
,SUM(amount) OVER (PARTITION BY per, companY) as 'Total'
FROM TestTable1
WHERE per = 'P1'
ORDER BY company ASC, account ASC;

This reply was modified 4 years, 7 months ago by John Mitchell-245523.
This reply was modified 4 years, 7 months ago by John Mitchell-245523.

scienceof8 Old Hand Points: 349 More actions · Answer 2

Thanks, this is perfect.

I was just looking at using a CTE when i saw your edit.

drew.allen SSC Guru Points: 76968 More actions · Answer 3

This is much more efficient. Note that it has a windowed function SUM() OVER() of an aggregate SUM().

SELECT
per
,company
,account
,COUNT(*) AS accNum
,SUM(amount) as accTotals
,SUM(SUM(amount)) OVER(PARTITION BY per, company) as Totals
FROM #TestTable1
WHERE per = 'P1'
GROUP BY per,company,account
ORDER BY company ASC, account ASC
SET STATISTICS IO, TIME OFF;

Drew

PS. You should not be using single quotes (') for column names. If you have to use something use square brackets ([ and ]), but I recommend not using anything unless absolutely required or you are using dynamic SQL.

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA