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_date

SQL 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_id

Related

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.

2 thoughts

Leave a Reply to Srini Cancel reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.