Menu
I have two tables: products and orders. Orders references products via ProductID as a foreign key. I want to know how many times each product has been sold, including the product being sold only once. I can almost get it to work using a left join, but that still gives one row with a count of one for all products, regardless of whether they exist in the orders table or not.
Is there a way to do this that will have you ending up with something like this?
Product | Times sold
Milk | 5
Bread | 18
Cheese | 0
... and so on.
388
SQL SELECT COUNT with WHERE clause SQL SELECT COUNT() can be clubbed with SQL WHERE clause. Using the WHERE clause, we have access to restrict the data to be fed to the COUNT() function and SELECT statement through a condition.
The SQL COUNT() function returns the number of rows in a table satisfying the criteria specified in the WHERE clause. It sets the number of rows or non NULL column values. COUNT() returns 0 if there were no matching rows. The above syntax is the general SQL 2003 ANSI standard syntax.
COUNT(*) returns the number of rows in a specified table, and it preserves duplicate rows. It counts each row separately. This includes rows that contain null values.
If you just do a COUNT(*), then you're counting products that have no orders as 1... instead, COUNT(o.OrderID), which will only count the records that have a non-null OrderID.
SELECT p.Product, COUNT(o.OrderID)
FROM
Products p LEFT JOIN
Orders o ON o.ProductID = p.ProductID
GROUP BY p.Product
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
