COUNT(*) returns the number of items in a group. This includes NULL values and duplicates.
COUNT(ALL expression) evaluates expression for each row in a group, and returns the number of nonnull values.
CREATE table customer(customer_id integer , customer_name varchar(20));
create table purchases(purchase_id integer , customer_id integer , purchase_description varchar(30));
INSERT INTO customer ( customer_id, customer_name )
VALUES ( 1, 'Marg' )
, ( 2, 'Ben' )
, ( 3, 'Phil' )
, ( 4, 'Steve' );
INSERT INTO purchases ( purchase_id, customer_id, purchase_description )
VALUES ( 1, 2, 'com/tag/5">500 Reams' )
, ( 2, 3, '6 toners' )
, ( 3, 3, '20 Staplers' )
, ( 4, 3, '2 Copiers' )
, ( com/tag/5">5, 3, '9 Name Plaques' );
SELECT c.customer_name
, COUNT(p.purchase_id) AS number_of_purchases
FROM customer c
LEFT JOIN purchases p
ON c.customer_id = p.customer_id
GROUP BY c.customer_name
manpreet![Tuteehub forum best answer]()
Best Answer
2 years ago
I want to display the number of purchases each customer has made. If they've made 0 purchases, I want to display 0.
Desired Output:
Customer Table:
Purchases Table:
My current query is as follows:
However, since it's a
LEFT JOIN, the query results in rows for customers with no purchases, which makes them part of theCOUNT(*). In other words, customers who've made 0 purchases are displayed as having made 1 purchase, like so:LEFT JOIN Output:
I've also tried an
INNER JOIN, but that results in customers with 0 purchases not showing at all:INNER JOIN Output:
How could I achieve my