CODEWITHSUNDEEP
sqlgoogle-bigquery
Anders Gustavsson
Anders Gustavsson

Reputation: 35

Sum for unique values

I have data stored as the tables shows below.

I'm grouping the Order Date by week and I want to show the total Quantity for each unique Order Number (without showing the actual Order Number column in my output).

Order Date Week Order Number Type Quantity
2022-10-01 40 123 A 2
2022-10-01 40 123 B 2
2022-10-01 40 345 C 3
2022-10-01 40 345 A 3
2022-10-01 40 345 B 3
2022-10-01 40 345 C 3
2022-10-11 42 456 B 1
2022-10-11 42 456 C 1
2022-10-12 42 789 A 3

I haven't been able to come up with a way to do the sum for Quantity per unique Order Number.

This is my desired ouput :

Week Quantity
40 5
42 4

Upvotes: 0

Views: 71

Answers (2)

NickW
NickW

Reputation: 9768

WITH unique_data AS (
    SELECT week, order_number, MAX(quantity) as quantity 
    FROM sample_table
    GROUP BY week, order_number
)
SELECT week, SUM(quantity)
FROM unique_data
GROUP BY week

Upvotes: 0

Jaytiger
Jaytiger

Reputation: 12234

You can consider below.

SELECT Week, SUM(Quantity) Quantity FROM (
  SELECT DISTINCT Week, Order_Number, Quantity 
    FROM sample_table
) GROUP BY 1;

+------+----------+
| Week | Quantity |
+------+----------+
|   40 |        5 |
|   42 |        4 |
+------+----------+

Upvotes: 0

Related Questions