I need to get a list of total sales grouped by the main category and Seller. Note that there can be sales on the main category (this is the best example I can think of at the moment).
Source table
+--------------------------------------+
|ID |Name |Seller|Qty|ParentID|
+--------------------------------------+
|10 |Egg |John |5 |NULL |
|10 |Egg |Anna |2 |NULL |
|10-01|Egg - Small |John |3 |10 |
|10-01|Egg - Small |Anna |4 |10 |
|10-02|Egg - Medium|John |2 |10 |
|10-02|Egg - Medium|Bob |11 |10 |
|10-03|Egg - Large |Anna |7 |10 |
+--------------------------------------+
Desired output
+------------------+
|ID|Name|Seller|Qty|
+------------------+
|10|Egg |John |10 | <- SUM of all sales John has made for any type of egg
|10|Egg |Anna |13 |
|10|Egg |Bob |11 |
+------------------+
I'm getting close with this query, but if someone has not made a sale on the main category, they will get the wrong Name when I use MIN(Name).
Current query
SELECT
SUBSTRING(t1.ID, 1, 2) AS 'ID',
MIN(t1.Name) AS 'Name',
t1.Seller,
SUM(t1.Qty) AS 'Qty'
FROM EggTest t1
GROUP BY
SUBSTRING(t1.ID, 1, 2),
t1.Seller
Current output
+--------------------------+
|ID|Name |Seller|Qty|
+--------------------------+
|10|Egg |Anna |13 |
|10|Egg - Medium|Bob |11 | <- Bob has not made sales on the main category
|10|Egg |John |10 |
+--------------------------+
EDIT: Seeing that multiple answers have already suggested SUBSTRING(Name, 1, 3) it will not work for me. Name does not always start with "Egg".
Update:
Now trying this query:
WITH report AS(
SELECT
ID = CASE WHEN s.ParentID IS NOT NULL THEN s.ParentID ELSE s.ID END,
Name = CASE WHEN s.ParentID IS NOT NULL THEN p.Name ELSE s.Name END,
s.Seller,
s.Qty
FROM EggTest s
LEFT JOIN EggTest p ON p.ID = s.ParentID
)
SELECT ID, Name, Seller, SUM(Qty) AS 'Total'
FROM report
GROUP BY ID, Name, Seller;
But I am getting this strange result:
+--------------------+
|ID|Name|Seller|Total|
+--------------------+
|10|Egg |Anna |24 | <- Wrong (Should be 13)
|10|Egg |Bob |22 | <- Wrong (Should be 11)
|10|Egg |John |15 | <- Correct(!!)
+--------------------+
In the report-table I'm getting some duplicates:
+------------------+
|ID|Name|Seller|Qty|
+------------------+
|10|Egg |John |5 |
|10|Egg |Anna |2 |
|10|Egg |John |3 |
|10|Egg |John |3 |
|10|Egg |Anna |4 |
|10|Egg |Anna |4 |
|10|Egg |John |2 |
|10|Egg |John |2 |
|10|Egg |Anna |7 |
|10|Egg |Anna |7 |
|10|Egg |Bob |11 |
|10|Egg |Bob |11 |
+------------------+
I will consider the source table name as [Sales]
You can use the following
with report as(
select ID = case when s.ParentID is not null then s.ParentID else s.ID end,
Name= case when s.ParentID is not null then p.Name else s.Name end,
s.Seller,
s.Qty
from Sales s
left join Sales p on p.ID = s.ParentID and p.Seller = s.Seller
)
select ID,Name,Seller,sum(Qty) as Qty
from report
group by ID,Name,Seller
Here a demo using Distinct
Here a demo by including the Seller in the left join , which will give you Name of the item for Seller Bob as NULL, the left join should work if you have correct data integrity which means separate table for the Items and Categories
replying on your last comment, here a demo how to make your data clear
Hope this will help you
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments