Help with SUM/GROUP BY issue

  • 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, 9 months ago by  scienceof8.
  • 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;
  • Thanks, this is perfect.

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

  • 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