February 7, 2020 at 3:22 pm
Hopefully somebody can help me with what i was hoping to be simple.
I have a simple dataset/table:
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:
...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:
How can i achieve the above?
This is a small part of a bigger script that will combine and compare/reconcile multiple periods (per).
February 7, 2020 at 3:30 pm
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;
February 7, 2020 at 4:18 pm
Thanks, this is perfect.
I was just looking at using a CTE when i saw your edit.
February 7, 2020 at 5:13 pm
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply