I need to create a sales summary based on data with an optional "Elected" flag, which is user controlled. The flag determines whether or not to group items belonging to the same class of item.
If none of the items within a class have the Elected flag set to 1, the items appear as individual rows in the summary. If one or more items in a class has the flag set to 1, the items within that class get grouped based on the first item that has the flag set (lowest item_id).
For example:
+--------+------+-------+-------+-----+---------+
| ItemId | Item | Class | Price | Qty | Elected |
+--------+------+-------+-------+-----+---------+
| 1 | A1 | 1 | 25.00 | 2 | 1 |
| 2 | A2 | 1 | 20.00 | 3 | 0 |
| 3 | A3 | 1 | 25.00 | 4 | 0 |
| 4 | B1 | 2 | 44.00 | 6 | 0 |
| 5 | B2 | 2 | 41.00 | 7 | 0 |
| 6 | C1 | 3 | 44.00 | 5 | 1 |
| 7 | D1 | 4 | 32.00 | 9 | 0 |
| 8 | D2 | 4 | 30.00 | 8 | 1 |
| 9 | D3 | 4 | 30.00 | 2 | 1 |
+--------+------+-------+-------+-----+---------+
Should give the following summary:
+-------+------+--------------+-------------+-----+
| Class | Item | Total Price* | Unit Price^ | Qty |
+-------+------+--------------+-------------+-----+
| 1 | A1 | 70.00 | 35.00 | 2 |
| 2 | B1 | 44.00 | 7.33 | 6 |
| 2 | B2 | 41.00 | 5.85 | 7 |
| 3 | C1 | 44.00 | 8.80 | 5 |
| 4 | D2 | 92.00 | 11.50 | 8 |
+-------+------+--------------+-------------+-----+
* Total Price: SUM() within class
^ Unit Price: Total Price / Qty of elected item
In the above table, Class 1 is summarised via A1, Class 3 gets summarised via C1, and Class 4 gets summarised via D2. Class 2 does not get summarised at all and instead appears fully listed.
The query below is a hack via multiple self-joins, but it works. Is there a more efficient way to tackle the problem?
SELECT Sales.Class,
coalesce(SalesWithFlagName.Item, SalesWithoutFlag._Item) 'Item',
SUM(Sales.Price) 'Total Price',
SUM(Sales.Price) / coalesce(SUM(Sales.Qty), SUM(SalesWithoutFlag._Qty)) 'Unit Price', /* Ignore possible Div0 error */
COALESCE(SUM(Sales.Qty), SUM(SalesWithoutFlag._Qty)) 'Qty'
FROM Sales
/* Class with at least one flag set: Get first item with flag */
LEFT JOIN (
SELECT Class _Class,
Elected _Elected,
MIN(ItemId) _ItemId
FROM Sales
GROUP BY Class, Elected
) AS SalesWithFlag ON (
Sales.ItemId = SalesWithFlag._ItemId
AND Sales.Elected = 1
)
/* Get item name from first Left Join */
LEFT JOIN Sales SalesWithFlagName ON (
SalesWithFlag._ItemId IS NOT NULL
AND SalesWithFlag._ItemId = SalesWithFlagName.ItemId
)
/* Class with at least flag not set: Get all items without flag */
LEFT JOIN (
SELECT Class _Class,
ItemId _ItemId,
Item _Item,
Qty _Qty
FROM Sales s2
WHERE NOT EXISTS (SELECT * FROM Sales s3 WHERE s3.Elected = 1 AND s2.Class = s3.Class)
GROUP BY Class, ItemId, Item, Qty
) AS SalesWithoutFlag ON (
Sales.ItemId = SalesWithoutFlag._ItemId
AND Sales.Elected = 0
)
WHERE SalesWithFlag._ItemId IS NOT NULL
OR SalesWithoutFlag._ItemId IS NOT NULL
GROUP BY Sales.Class,
COALESCE(SalesWithFlagName.Item, SalesWithoutFlag._Item)
You need both individual rows and aggregates, so this is a task for a Group Sum and some additional logic:
WITH cte AS
(
SELECT *,
-- either the individual Price or the Group Sum
CASE
WHEN Elected = 1
THEN SUM(Price) OVER (PARTITION BY Class)
ELSE Price
END AS TotalPrice,
-- is there any Elected = 1 within the Class?
MAX(Elected) OVER (PARTITION BY Class) AS maxElected,
-- Find the first row with Elected = 1
ROW_NUMBER() OVER (PARTITION BY Class ORDER BY Elected DESC, ItemId) AS rn
FROM Sales
)
SELECT
class,
Item,
TotalPrice,
TotalPrice / Qty AS UnitPrice,
QTy
FROM cte
WHERE maxElected = 0 -- no Elected = 1
OR rn = 1 -- first row with Elected = 1
ORDER BY 1,2
;
See Fiddle
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments