I have a table like below:
| Category | Item1 | Child of Item1 | Date | Week |
|---|---|---|---|---|
| A | 3 | 2 | 1 | 1 |
| A | 3 | 7 | 1 | 1 |
| A | 4 | 1 | 2 | 1 |
| B | 4 | 2 | 1 | 1 |
| B | 5 | 6 | 2 | 1 |
| A | 2 | 2 | 1 | 2 |
| A | 1 | 3 | 2 | 2 |
| B | 2 | 2 | 1 | 2 |
| B | 2 | 1 | 2 | 2 |
Item1 column has a child that sometimes generate 2 rows with same date, week and category. How do I use SQL to sum quantity of item1 and child of item1 by week and category but ignore duplicate data in item1 to get data like below:
| Category | Item1 | Child of Item1 | Week |
|---|---|---|---|
| A | 7 | 10 | 1 |
| B | 9 | 8 | 1 |
| A | 3 | 5 | 2 |
| B | 4 | 3 | 2 |
CodePudding user response：
You can GROUP BY category and week and then use SUM to aggregate and take only the DISTINCT values for item1:
SELECT category,
SUM(DISTINCT item1) AS item1,
SUM(child_of_item1) AS child_of_item1,
week
FROM table_name
GROUP BY category, week
ORDER BY category, week;
Which, for the sample data:
CREATE TABLE table_name (Category, Item1, Child_of_Item1, "Date", Week) AS
SELECT 'A', 3, 2, 1, 1 FROM DUAL UNION ALL
SELECT 'A', 3, 7, 1, 1 FROM DUAL UNION ALL
SELECT 'A', 4, 1, 2, 1 FROM DUAL UNION ALL
SELECT 'B', 4, 2, 1, 1 FROM DUAL UNION ALL
SELECT 'B', 5, 6, 2, 1 FROM DUAL UNION ALL
SELECT 'A', 2, 2, 1, 2 FROM DUAL UNION ALL
SELECT 'A', 1, 3, 2, 2 FROM DUAL UNION ALL
SELECT 'B', 2, 2, 1, 2 FROM DUAL UNION ALL
SELECT 'B', 2, 1, 2, 2 FROM DUAL;
Outputs:
CATEGORY ITEM1 CHILD_OF_ITEM1 WEEK A 7 10 1 A 3 5 2 B 9 8 1 B 2 3 2
db<>fiddle here
CodePudding user response：
Select
ColumnA,
ColumnB,
(ColumnA ColumnB) As Total
From
Table_Name
Group By Column_name