I'm having issues using DISTINCT
and COUNT
in the same query. I would like to get unique items then get a total count for each month. (I've done this in two queries but I know there's a better way.)
Query:
SELECT
FORMAT_TIMESTAMP('%Y-%m', Sell_Date) AS Sell_Date,
count(*) Total_Sold
FROM
`project.dataset.items`
GROUP BY
Sell_Date
ORDER BY
Sell_Date
Results (snippet):
Row Sell_Date Total_Sold
1 2010-05 15
2 2010-06 40
3 2010-07 75
4 2010-08 20
This is what I would like, however, this contains duplicate Item_Id
entries.
If I replace the SELECT
above with this:
SELECT
DISTINCT Item_Id,
FORMAT_TIMESTAMP('%Y-%m', Sell_Date) AS Sell_Date,
I get this error:
Error: SELECT list expression references column Item_Id which is neither grouped nor aggregated at [2:12]
If I replace the GROUP BY
with:
GROUP BY
Sell_Date, Item_Id
Results (snippet):
Row Item_Id Sell_Date Total_Sold
1 992 2010-05 1
2 118 2010-05 1
3 855 2010-05 1
4 846 2010-05 1
5 989 2010-05 1
6 505 2010-05 1
7 997 2010-05 1
8 983 2010-05 1
9 122 2010-05 1
10 601 2010-05 1
11 845 2010-05 1
How can I get DISTINCT
items then count the total for each month?
Best Answer
count (distinct item_id)