Summarizing Raw Sales Data with Group Summaries and Final Summary

  • Greetings,

    I need to transform data returned in a query from several tables as shown below to the desired output  shown below. This looks like some type of rollup and I have tried Grouping by State and Department but I can't get it to display the sums as shown. I have to include the non aggregated data in the Group by and I think this is throwing this off. I looked at using Grouping Sets but that didn't work for me either. Any idea of the correct approach to restructure this data into something approximating what is shown below.  The intent is to display the raw groups of data by State and Department and sum the financial info by Group and State below the raw data and display a Grand Summary below.  I've also tried using a Union all approach with some success but I suspect Rollup is my best approach. Another option I looked at was defining variables and populating all of the sums into a temp table in a SP and pulling the data out from there. That seems to work bit is a very bulky way to go about it. Any direction or advice would be appreciated.

     

    State   Dept   ID   Client     Date                 Retail       Base       Tax

    NY      A         10    Joe D      1/10/2024      71.44        68.04      3.4

    CA      B         15    Paul D     2/23/2024     43.46        40           3.46

    NY     A          14   Jim R       3/14/2024     51.45         49            2.45

    NY     B           12    Al J         2/13/2024     38.85       37             1.85

    NY     B            18   Jay H     2/15.2024      82.60       80           2.60

    CA     B            16    Pete Y    2/18/2024      65.4         64            1.40

    CA     A            20   Joe P    2/11/2024        73.5          70             3.5


    Desired Output

    State                Dept   ID   Client           Date              Retail       Base       Tax

    NY                     A         10    Joe D      1/10/2024         71.44        68.04      3.4

    NY                     A         14   Jim R       3/14/2024         51.45         49           2.45

    NY                     A                                                               122.89      117.04     5.85

    NY                     B         12    Al J         2/13/2024             38.85       37             1.85

    NY                     B          18   Jay H      2/15.2024              82.60       80           2.60

    NY                     B                                                                   121.45      117.0        4.45

    State Summary: NY

    Sum (Retail)  244.34

    Sum (Base)    234.04

    Sum (Tax)     10.30

    CA                      A               20   Joe P    2/11/2024            73.5          70             3.5

    CA                     A                                                                    73.5          70              3.5

    CA                     B                15    Paul D     2/23/2024       43.46        40           3.46

    CA                     B                 16    Pete Y    2/18/2024          65.4         64            1.40

    CA                     B                                                                      108.86    104           4.86

    State Summary  CA

    Sum (Retail)  182.36

    Sum (Base)    174

    Sum (Tax)    8.36

    Grand Summary:

    Sum (Retail)   426.7

    Sum (Base)   408.04

    Sum (Tax)    18.66

  • Please provide your sample data in consumable format (in the form of CREATE TABLE/INSERT statements).

    If the format of your desired output is exactly as stated, you would be better off using a reporting tool to produce it. Any straight T-SQL solution is limited by the fact that the number of columns and their datatypes is fixed in the final output.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • This is about as close as you can get using SQL.  If you want more control over your formatting, you should use a reporting tool.

    SELECT CASE WHEN GROUPING(v.Dept) = 0 THEN v.[State]
    WHEN GROUPING(v.[State]) = 0 THEN CONCAT('State Summary: ', v.[State])
    ELSE 'Grand Summary:'
    END AS [State]
    , v.Dept
    , v.ID
    , v.Client
    , v.[Date]
    , SUM(Retail) AS Retail
    , SUM(Base) AS Base
    , SUM(Tax) AS Tax
    FROM
    ( -- Use your table here
    VALUES ('NY', 'A', 10, 'Joe D', '1/10/2024', 71.44, 68.04, 3.4)
    , ('CA', 'B', 15, 'Paul D', '2/23/2024', 43.46, 40, 3.46)
    , ('NY', 'A', 14, 'Jim R', '3/14/2024', 51.45, 49, 2.45)
    , ('NY', 'B', 12, 'Al J', '2/13/2024', 38.85, 37, 1.85)
    , ('NY', 'B', 18, 'Jay H', '2/15.2024', 82.60, 80, 2.60)
    , ('CA', 'B', 16, 'Pete Y', '2/18/2024', 65.4, 64, 1.40)
    , ('CA', 'A', 20, 'Joe P', '2/11/2024', 73.5, 70, 3.5)
    ) v([State], Dept, ID, Client, [Date], Retail, Base, Tax)
    GROUP BY GROUPING SETS((), (v.[State]), (v.[State], v.Dept), (v.[State], v.Dept, v.ID, v.Client, v.[Date]))
    ORDER BY GROUPING(v.[State]), v.[State] DESC, GROUPING(v.Dept), v.Dept, GROUPING(v.ID), v.ID

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • This is exactly what I was trying to do. Thanks very much. The grouping was done by a CRM app but it also nested the content and the formatting was garbled when exported to Excel so we needed to rebuild it. I learned some new tricks with grouping sets

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

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