yandex
loader

please wait

  • Brian Burl Feb-12-2019 07:01:39 AM ( 5 days 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_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 

  • Rahul Chaudhary Feb-12-2019 07:03:22 AM ( 5 days ago )

    SUM(ISNULL(purchases.customer_id)) AS number_of_purcahses

  • Liza Sain Feb-12-2019 07:04:35 AM ( 5 days ago )


    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

  • Priyanka Chadda Feb-12-2019 07:05:51 AM ( 5 days ago )

    customer_name is not a part of group by. won't work

  • Imran Malik Feb-12-2019 07:08:36 AM ( 5 days ago )

    5.7+ will allow this, even in set sql_mode=ONLY_FULL_GROUP_BY, provided customer_id is a PK or unique not null secondary key. When the group by field is unique, other rows from the same table can't be an invalid aggregation as their is only one value possible. 

  • Karlie Kloss Feb-12-2019 07:09:31 AM ( 5 days ago )

    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_name
  • Deepak Parmar Feb-12-2019 07:10:39 AM ( 5 days ago )

    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
     
  • Ayushi Jain Feb-12-2019 07:11:47 AM ( 5 days ago )

    COUNT(*) counts rows. You want to count matches, so count from the second table as following:

    select customer.customer_name , a.number_of_purchases from (
    SELECT customer_id, COUNT(purchases.purchase_id) AS number_of_purchaes 
    FROM customer 
    LEFT JOIN purchases ON customer.customer_id = purchases.customer_id 
    GROUP BY customer.customer_id) as a 
    inner join customer on customer.customer_id=a.customer_id;

    In other words, the LEFT JOIN returns a row when there is no match. That row has a NULL value for all the columns in the purchases table.

     
  • Vikrant Srivastava Feb-12-2019 07:12:50 AM ( 5 days ago )
    SELECT 
      customer_name, COUNT(purchase_id) AS number_of_purchases
    FROM
      customer AS c
    LEFT JOIN purchases AS p ON (c.cid = p.cid)
    GROUP BY c.name
  • Katie George Feb-12-2019 07:14:16 AM ( 5 days ago )

    Instead of count(*) use COUNT(purchases.customer_id)

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

Please login

Similar Discussion

Recommended For You