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

  • A+
Category:Languages

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?

 


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

Comment

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: