SQL - LEFT JOIN, but I want COUNT(*) to only count the results from the INNER part of the join

Question

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_name | number_of_purchases |
 -------------------------------------
|    Marg       |          0          |
|    Ben        |          1          |
|    Phil       |          4          |
|    Steve      |          0          |
 -------------------------------------

Customer Table:

 -----------------------------
| customer_id | customer_name |
 -----------------------------
|      1      |      Marg     |
|      2      |      Ben      |
|      3      |      Phil     |
|      4      |      Steve    |
 -----------------------------

Purchases Table:

 --------------------------------------------------
| purchase_id | customer_id | purchase_description |
 --------------------------------------------------
|      1      |       2     |     500 Reams        |
|      2      |       3     |     6 Toners         |
|      3      |       3     |     20 Staplers      |
|      4      |       3     |     2 Copiers        |
|      5      |       3     |     9 Name Plaques   |
 --------------------------------------------------

---

My current query is as follows:

SELECT customer_name, COUNT(*) AS number_of_purchaes 
FROM customer 
LEFT JOIN purchases ON customer.customer_id = purchases.customer_id 
GROUP BY customer.customer_id

However, since it's a LEFT JOIN, the query results in rows for customers with no purchases, which makes them part of the COUNT(*). In other words, customers who've made 0 purchases are displayed as having made 1 purchase, like so:

LEFT JOIN Output:

 -------------------------------------
| customer_name | number_of_purchases |
 -------------------------------------
|    Marg       |          1          |
|    Ben        |          1          |
|    Phil       |          4          |
|    Steve      |          1          |
 -------------------------------------

I've also tried an INNER JOIN, but that results in customers with 0 purchases not showing at all:

INNER JOIN Output:

 -------------------------------------
| customer_name | number_of_purchases |
 -------------------------------------
|    Ben        |          1          |
|    Phil       |          4          |
 -------------------------------------

How could I achieve my Desired Output where customers with 0 purchases are shown?

Answer 1

Instead of count(*) use count(purchase_id)

SELECT customer_name, COUNT(purchase_id) AS number_of_purchaes 
FROM customer 
LEFT JOIN purchases ON customer.customer_id = purchases.customer_id 
GROUP BY customer_id,customer_name

Answer 2

You can try like this:

Sample Data:

create table customer(customer_id integer, customer_name varchar(20));

create table purchaser(purchaser_id varchar(20), customer_id integer, description varchar(20));


insert into customer values(1, 'Marg');
insert into customer values(2, 'Ben');
insert into customer values(3, 'Phil');
insert into customer values(4, 'Steve');

insert into purchaser values(1, 2, '500 Reams');
insert into purchaser values(2, 3, '6 toners');
insert into purchaser values(3, 3, '20 Staplers');
insert into purchaser values(4, 3, '20 Staplers');
insert into purchaser values(5, 3, '20 Staplers');

SELECT c.customer_id, c.customer_name, COUNT(p.purchaser_id) AS number_of_purchaes 
FROM customer c
LEFT JOIN purchaser p ON c.customer_id = p.customer_id 
GROUP BY c.customer_id;

SQL fiddle: http://sqlfiddle.com/#!9/32ff0a/2

Answer 3

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, '500 Reams' )
     , ( 2, 3, '6 toners' )
     , ( 3, 3, '20 Staplers' )
     , ( 4, 3, '2 Copiers' )
     , ( 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