TOP, SUM SQL Statement

Question

I have a table called RPT with the following columns:

1. CODENUMBER
2. AXSUBGROUP
3. ITEMSTRUCTURE
4. UNITSSOLD
5. DOLLARS

I need to return to the user the TOP 20 AXSUBGROUPS based on UNITSSOLD along with the UNITSSOLD and DOLLARS values. In addition, I need to return the ITEMSTRUCTURES that belong to these top 20 AXSUBGROUPS (joined by codenumber), along with their UNITSSOLD AND DOLLARS.

I'm not sure which way to go about querying this as I need to group by AXSUBGROUP and then by ITEMSTRUCTURE but ITEMSTRUCTURE is tied to aXSUBGROUP by CODENUMBER.

I'd appreciate any help you can give.

Thank you.

EDIT To better illustrate my needs:

1. BAR - 10,000
   • BAR 2 oz - 5,000
   • BAR 1 oz - 5,000
2. GIFT BOX - 8,000
   • TRUFFLE BOX - 5,000
   • ACRYLIC BOX - 3,000

...

Here's sample data in the table (sorry - couldn't figure out how to format a table...)

CodeNumber UnitsSold Dollars AxSubGroup ItemStructure
ABC123    |    9    | 500   | Bar      | 1 oz
ABC456    |    9    | 800   | Bar      | 2 oz
ABC789    |    9    | 500   | Bar      | 3 oz
DEF123    |    3    | 200   | Tin      | Round
DEF456    |    2    | 200   | Tin      | Rectangular
GHI123    |    1    | 200   | Truffle  | Luxe
GHI456    |    5    | 100   | Truffle  | Executive
GHI789    |    4    | 200   | Truffle  | Eco
JKL123    |    9    | 500   | Box      | 2 pc
JKL456    |    5    | 100   | Box      | 4 pc
JKL789    |    5    | 100   | Box      | 8 pc

... If it helps, originally this was 2 tables - RPT (CodeNumber, UnitsSold, Dollars) and FLEX (CodeNumber, AxSubGroup, ItemStructure).

Answer 1

So it seems like you need to:

1. Calculate group totals.

2. Rank groups by the totals.

3. Get the details of the groups that are in the top 20 UNION the groups that are in the top 20.

So maybe something like this:

;
WITH RPT (CodeNumber, UnitsSold, Dollars, AxSubGroup, ItemStructure) AS (
  SELECT 'ABC123', 9, 500, 'Bar'    , '1 oz'        UNION ALL
  SELECT 'ABC456', 9, 800, 'Bar'    , '2 oz'        UNION ALL
  SELECT 'ABC789', 9, 500, 'Bar'    , '3 oz'        UNION ALL
  SELECT 'DEF123', 3, 200, 'Tin'    , 'Round'       UNION ALL
  SELECT 'DEF456', 2, 200, 'Tin'    , 'Rectangular' UNION ALL
  SELECT 'GHI123', 1, 200, 'Truffle', 'Luxe'        UNION ALL
  SELECT 'GHI456', 5, 100, 'Truffle', 'Executive'   UNION ALL
  SELECT 'GHI789', 4, 200, 'Truffle', 'Eco'         UNION ALL
  SELECT 'JKL123', 9, 500, 'Box'    , '2 pc'        UNION ALL
  SELECT 'JKL456', 5, 100, 'Box'    , '4 pc'        UNION ALL
  SELECT 'JKL789', 5, 100, 'Box'    , '8 pc'
),
totals AS (
  SELECT
    *,
    TotalUnitsSold = SUM(UnitsSold) OVER (PARTITION BY AxSubGroup),
    TotalDollars   = SUM(Dollars  ) OVER (PARTITION BY AxSubGroup)
  FROM RPT
),
ranked AS (
  SELECT
    *,
    rnk = DENSE_RANK() OVER (ORDER BY TotalDollars DESC, AxSubGroup)
  FROM totals
)

SELECT
  rnk,
  AxSubGroup,
  ItemStructure,
  UnitsSold,
  Dollars
FROM ranked
WHERE rnk <= 3

UNION ALL

SELECT DISTINCT
  rnk,
  AxSubGroup,
  NULL,
  TotalUnitsSold,
  TotalDollars
FROM ranked
WHERE rnk <= 3

ORDER BY rnk, AxSubGroup, ItemStructure

The results are:

rnk  AxSubGroup  ItemStructure  UnitsSold    Dollars
---  ----------  -------------  -----------  -----------
1    Bar         NULL           27           1800
1    Bar         1 oz           9            500
1    Bar         2 oz           9            800
1    Bar         3 oz           9            500
2    Box         NULL           19           700
2    Box         2 pc           9            500
2    Box         4 pc           5            100
2    Box         8 pc           5            100
3    Truffle     NULL           10           500
3    Truffle     Eco            4            200
3    Truffle     Executive      5            100
3    Truffle     Luxe           1            200

Answer 2

Need a better example of the expected output matching the sample source data. There remains quite a bit of ambiguity with the question. But here is another stab at it:

Using the sample source data with 4 "AxSubGroup" items ("Bar", "Tin", Truffle", "Box"), this solution restricts to the top 3 AxSubGroups (rather than the top 20).

The "top" subquery sums "UnitsSold" for each "AxSubGroup", orders them in descending order, numbers them, and grabs the top 3. Here is the result before the last entry gets shaved off:

 axsubgroup | totalsold | rownum
------------+-----------+--------
 Bar        |        27 |      1
 Box        |        19 |      2
 Truffle    |        10 |      3
 Tin        |         5 |      4
(4 rows)

This set is then joined back to the original RPT table to get the details for each one of these top axsubgroup items.

CREATE TABLE rpt(CodeNumber varchar(6), UnitsSold int, Dollars int, AxSubGroup varchar(32), ItemStructure varchar(32));

INSERT INTO rpt VALUES('ABC123', 9, 500, 'Bar', '1 oz');
INSERT INTO rpt VALUES('ABC456', 9, 800, 'Bar', '2 oz');
INSERT INTO rpt VALUES('ABC789', 9, 500, 'Bar', '3 oz');
INSERT INTO rpt VALUES('DEF123', 3, 200, 'Tin', 'Round');
INSERT INTO rpt VALUES('DEF456', 2, 200, 'Tin', 'Rectangular');
INSERT INTO rpt VALUES('GHI123', 1, 200, 'Truffle', 'Luxe');
INSERT INTO rpt VALUES('GHI456', 5, 100, 'Truffle', 'Executive');
INSERT INTO rpt VALUES('GHI789', 4, 200, 'Truffle', 'Eco');
INSERT INTO rpt VALUES('JKL123', 9, 500, 'Box', '2 pc');
INSERT INTO rpt VALUES('JKL456', 5, 100, 'Box', '4 pc');
INSERT INTO rpt VALUES('JKL789', 5, 100, 'Box', '8 pc');


WITH top AS (

     -- Top AxSubGroups based on UnitsSold with order number assigned, restricted to top 20
    SELECT AxSubGroup, totalSold, rowNum
      FROM (
             -- Top AxSubGroups based on UnitsSold with order number assigned
             SELECT AxSubGroup, totalSold
                   ,ROW_NUMBER() OVER (ORDER BY totalSold DESC) AS rowNum
               FROM ( 
                      -- Top AxSubGroups based on UnitsSold
                      -- (do you really want this 'UnitsSold' or rather on 'gross': 'UnitsSold' * 'Dollars'?)
                      SELECT AxSubGroup, SUM(UnitsSold) AS totalSold
                        FROM rpt r
                        GROUP BY AxSubGroup
                        ORDER by totalSold DESC
                    ) x
               ORDER BY totalSold DESC
           ) y
      WHERE rowNum <= 3
)

SELECT t.AxSubGroup, t.totalSold, r.ItemStructure, r.UnitsSold, r.Dollars
  FROM top t
      ,rpt r
  WHERE r.AxSubGroup = t.AxSubGroup;

 axsubgroup | totalsold | itemstructure | unitssold | dollars
------------+-----------+---------------+-----------+---------
 Bar        |        27 | 1 oz          |         9 |     500
 Bar        |        27 | 2 oz          |         9 |     800
 Bar        |        27 | 3 oz          |         9 |     500
 Truffle    |        10 | Luxe          |         1 |     200
 Truffle    |        10 | Executive     |         5 |     100
 Truffle    |        10 | Eco           |         4 |     200
 Box        |        19 | 2 pc          |         9 |     500
 Box        |        19 | 4 pc          |         5 |     100
 Box        |        19 | 8 pc          |         5 |     100
(9 rows)

The first two columns of the result are summary/total columns which are repeated for each member of the group, while the last three columns are the details which comprise the sum.

Answer 3

SELECT 
      RPT.CodeNumber
    , RPT.AxSubgroup
    , RPT.ItemStructure
    , RPT.UnitsSold
    , RPT.Dollars
    , grp.TotalUnitsSold
FROM 
      RPT
  JOIN
      ( SELECT TOP 20 
              AxSubgroup
            , SUM(UnitsSold) AS TotalUnitsSold
        FROM
              RPT 
        GROUP BY
              AxSubgroup
        ORDER BY
              TotalUnitsSold DESC
      ) AS grp
    ON 
      grp.AxSubgroup = RPT.AxSubgroup
ORDER BY
      RPT.AxSubgroup 

Answer 4

Looks like it's ROW_NUMBER to the rescue!

SELECT CODENUMBER
    , AXSUBGROUP
    , ITEMSTRUCTURE
    , UNITSSOLD
    , DOLLARS
FROM
(
    SELECT CODENUMBER
        , AXSUBGROUP
        , ITEMSTRUCTURE
        , UNITSSOLD
        , DOLLARS
        , ROW_NUMBER() OVER (PARTITION BY AXSUBGROUP ORDER BY UNITSSOLD DESC) AS r
    FROM RPT
) a
WHERE r <= 20