Post reply

SUM Function Group by Status and User displayed on columns

  • November 22, 2018 at 7:26 am

    #378652

    Dear all,
    Currently I am doing some reports that need one T-SQL Query as data source and I was wondering if there would be one solution for a tricky sum function group by UserName and Status.
    Here is the data we are using to do the sum

    UserNameStatusNbOrdersTotalAmount
    User1Accepted395000
    User1Cancelled31250
    User1Completed41200
    User1Created239000
    User1Declined43863
    User1Issued389145269
    User1Underway10
    User2Accepted412000
    User2Created187150000
    User2Issued24600
    User3Accepted198600
    User3Cancelled6350
    User3Created22140000

    And the result I would like to fetch would be to have only 1 user per row. The grouping should be base on username and status as follows:
    NbPending = SUM of NbOders with status "Created" and "Issued"
    NbDeclined = SUM of NbOrder with status "Cancelled" and "Declined"
    NbAccepted = SUM of NbOrders with status "Accepted" and "Completed"

    If we apply those rules, we would get the result table below:

    UserNameNbPendingAmtPendingNbDeclinedAmtDeclinedNbAcceptedAmtAcceptedTotalOrdersTotalAmntOrders
    User1416154269741113395000529160382
    User218915460000412000193166600
    User322140000635019860047148950

    Can we achieve this though one only query (Using grouping and subqueries) ? 
    For now I work with some temporary tembles containing parts of information and grouping them by UserName.

    Thanks in advance for any help you could give in order to optimise my TSQL Code.

    Kind regards,
    Octavio ROMANO

  • November 22, 2018 at 8:21 am

    #2014342

    IF OBJECT_ID('tempdb..#table1','U') IS NOT NULL DROP TABLE #table1
    SELECT *
    INTO #table1
    FROM (VALUES
    ('User1','Accepted',39,5000),
    ('User1','Cancelled',31,250),
    ('User1','Completed',4,1200),
    ('User1','Created',23,9000),
    ('User1','Declined',43,863),
    ('User1','Issued',389,145269),
    ('User1','Underway',1,0),
    ('User2','Accepted',4,12000),
    ('User2','Created',187,150000),
    ('User2','Issued',2,4600),
    ('User3','Accepted',19,8600),
    ('User3','Cancelled',6,350),
    ('User3','Created',22,140000)
    ) T(UserName, Status, NbOrders, TotalAmount)
    GO
    SELECT t.UserName,
           SUM(IIF(t.Status IN('Created','Issued'),t.NbOrders,0)) NbPending,
           SUM(IIF(t.Status IN('Created','Issued'),t.TotalAmount,0)) AmtPending,
           SUM(IIF(t.Status IN('Cancelled','Declined'),t.NbOrders,0)) NbDeclined,
           SUM(IIF(t.Status IN('Cancelled','Declined'),t.TotalAmount,0)) AmtDeclined,
           SUM(IIF(t.Status IN('Accepted','Completed'),t.NbOrders,0)) NbCompleted,
           SUM(IIF(t.Status IN('Accepted','Completed'),t.TotalAmount,0)) AmtCompleted,
           SUM(t.NbOrders) TotalOrders,
           SUM(t.TotalAmount) TotalAmntOrders
      FROM #table1 t
     GROUP BY t.UserName

  • November 22, 2018 at 9:24 am

    #2014344

    Hello SSCrazy Eights,
    Just tried it and SQL does not accept this structure. the IIF with the "IN" does not work.
    Thanks anyways for your answer.
    Kind regards,
    Octavio

  • November 22, 2018 at 9:29 am

    #2014345

    octavioromano - Thursday, November 22, 2018 9:24 AM

    Hello SSCrazy Eights,
    Just tried it and SQL does not accept this structure. the IIF with the "IN" does not work.
    Thanks anyways for your answer.
    Kind regards,
    Octavio

    IIF isn't in SQL 2008 so you can use CASE instead:
    SELECT t.UserName,
           SUM(CASE WHEN t.Status IN('Created','Issued') THEN t.NbOrders ELSE 0 END) NbPending,
           SUM(CASE WHEN t.Status IN('Created','Issued') THEN t.TotalAmount ELSE 0 END) AmtPending,
           SUM(CASE WHEN t.Status IN('Cancelled','Declined') THEN t.NbOrders ELSE 0 END) NbDeclined,
           SUM(CASE WHEN t.Status IN('Cancelled','Declined') THEN t.TotalAmount ELSE 0 END) AmtDeclined,
           SUM(CASE WHEN t.Status IN('Accepted','Completed') THEN t.NbOrders ELSE 0 END) NbCompleted,
           SUM(CASE WHEN t.Status IN('Accepted','Completed') THEN t.TotalAmount ELSE 0 END) AmtCompleted,
           SUM(t.NbOrders) TotalOrders,
           SUM(t.TotalAmount) TotalAmntOrders
    FROM #table1 t
    GROUP BY t.UserName

  • November 22, 2018 at 1:29 pm

    #2014349

    Beautiful, works like a charm.
    Thanks a lot for your help.
    Kind regards,
    Octavio

  • November 22, 2018 at 2:08 pm

    #2014356

    octavioromano - Thursday, November 22, 2018 1:29 PM

    Beautiful, works like a charm.
    Thanks a lot for your help.
    Kind regards,
    Octavio

    The question now is, if you were given a similar problem, could you do it without outside help?  In other words, do you understand how and why this method works?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 6 posts - 1 through 5 (of 5 total)

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