SQL Count Vs Count Distinct With Group By
How to know the products for sale each market date or how many different products each vendor has offered? You can determine these values using COUNT and COUNT DISTINCT.
SQL Count GROUP BY
COUNT will count up the rows within a group when used with GROUP BY, and COUNT DISTINCT will count up the unique values present ( in the specified field within the group.)
To determine how many products are for sale on each market date, you can count the rows in the vendor_inventory table grouped by date.
It doesn’t tell us what quantity each product was offered or sold. But it counts available products. It is because a row is present in the table for each product for each vendor for each market date.
SELECT market_date, COUNT(product_id) AS product_count
FROM farmers_market.vendor_inventory
GROUP BY market_date
ORDER BY market_dateSQL Count DISTINCT Group By
If you want to know how many different products—with unique product IDs—each vendor brought to market during a date range. So you can use COUNT DISTINCT on the product_id field:
SELECT vendor_id, COUNT(DISTINCT product_id) AS different_products_offered
FROM farmers_market.vendor_inventory
WHERE market_date BETWEEN '2019-03-02' AND '2019-03-16'
GROUP BY vendor_id
ORDER BY vendor_idRelated
