CODEWITHSUNDEEP
sqlms-access
BluPhoenix28
BluPhoenix28

Reputation: 1

Counting Distinct Items based on Conditional Statments

Okay, I'm pretty sure this has been answered somewhere, but I can't seem to find it. I also apologizes if I don't follow proper (read as helpful) etiquette on asking this question. My assumption is that the answer is simple, but my SQL is virtually non-existant and I am at my wits end.

I have two tables in MS Access. I am having trouble getting the desired count with a SELECT DISTINCT query

**Table1: **Sales Products - Each product that we have sold

Job Number Product
0001 Window
0001 Window
0001 Door
0002 Window
0002 Roof
0002 Door
0003 Siding

**Table 2: **Products - A List of all Products that we sell

Product ID
Window 1
Door 2
Siding 3
Roof 4

**My desired goal: **A Query that lists how many jobs included a particular product (example: Window) despite how many of that product they would have ordered. Example Below:

Product Job Count
Window 2
Door 2
Siding 1
Roof 1

I can just feel that I'm missing something simple, but is it a conditional clause with the SELECT DISTINCT? or something else entirely?

I've tried every SELECT DISTINCT forum posting solution I can think of but I can't seem to get it.

Upvotes: 0

Views: 27

Answers (1)

CChartrand
CChartrand

Reputation: 1

SELECT product, COUNT(DISTINCT job) as job_count   
FROM sales_product  
GROUP BY product

This will count the distinct number of jobs associated with each product.
If your sales_product table uses a product_id instead of the product name, you will need a join in your query as follows:

SELECT p.product, COUNT(DISTINCT job) as job_count  
FROM sales_product sp  
INNER JOIN product p on sp.product_id = p.id  
GROUP BY p.product

Note: Any products without any associated jobs will be omitted from the results. If you want to include products with a job_count of zero, you would want to do something similar but placing the count within a subquery.

Upvotes: 0

Related Questions