Using DISTINCT and COUNT in a query

countdistinctgoogle-bigquery

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)


SELECT
  FORMAT_TIMESTAMP('%Y-%m', Sell_Date) AS Sell_Date,
  count(*) Total_Sold,
  count (distinct item_id) as distinct_item_id 

FROM
  `project.dataset.items`
GROUP BY
  FORMAT_TIMESTAMP('%Y-%m', Sell_Date) AS Sell_Date
ORDER BY
  Sell_Date