CODEWITHSUNDEEP
mysql
genuy11512
genuy11512

Reputation: 33

Count how many customers that they just have 1 order in MySQL

I'm using MySQL database server. My query is:

Count how many customers that they just have 1 order and how many customers that they have more than 1 orders.

This is my SQL query:

SELECT 
    COUNT((SELECT 
            customer_code
        FROM
            customer AS c
                LEFT JOIN
            order_info AS oi ON (c.customer_code = oi.customer_code)
        GROUP BY customer_code
        HAVING COUNT(id_order) = 1)) AS New_customers

How can I get this result.

Upvotes: 0

Views: 2182

Answers (5)

AmazingDreams
AmazingDreams

Reputation: 3204

You are grouping by customer_code before counting, I think this will group the rows together thus effectively removing all orders from the resultset. HAVING will always use the result set as the 'data feed'. 

SELECT 
    COUNT(DISTINCT customer_code)
FROM
    customer AS c
LEFT JOIN order_info AS oi ON (c.customer_code = oi.customer_code)

HAVING COUNT(id_order) = 1

OR a lot simpler (but perhaps not more efficient)

SELECT 
    COUNT(customer_code)
FROM
    customer AS c
WHERE (
    SELECT 
        COUNT(*) 
    FROM `order_info` 
    WHERE 
        `customer_code` = `c`.`customer_code`
) = 1

To get the number of customers with more than one order, simply change the = into > where appropiate.

Upvotes: 2

uvais
uvais

Reputation: 416

try this: 

  SELECT * FROM ( select c.customer_code, count(*) total FROM customer c
  LEFT JOIN
  order_info AS oi ON (c.customer_code = oi.customer_code) 
  GROUP BY customer_code) test
  CASE total
  WHEN 1 THEN SELECT total one_order FROM test;
  ELSE 
  SELECT total more_order FROM test

Upvotes: 0

user2660030
user2660030

Reputation:

Try this

SELECT 
count(*)
FROM
customer AS c
LEFT JOIN
order_info AS oi ON c.customer_code = oi.customer_code
HAVING COUNT(id_order) = 1

Upvotes: 0

ASNAOUI Ayoub
ASNAOUI Ayoub

Reputation: 472

For customers having 1 order

SELECT count(customer_code) FROM customer AS c
INNER JOIN order_info AS oi ON (c.customer_code = oi.customer_code)
GROUP BY customer_code HAVING COUNT(id_order) = 1))


 For customers having MORE THAn 1 order

SELECT count(customer_code) FROM customer AS c
INNER JOIN order_info AS oi ON (c.customer_code = oi.customer_code)
GROUP BY customer_code HAVING COUNT(id_order) > 1))

Upvotes: 0

Praveen Prasannan
Praveen Prasannan

Reputation: 7133

SELECT 
count(customer_code)
FROM
customer AS c
LEFT JOIN
order_info AS oi ON (c.customer_code = oi.customer_code)
GROUP BY customer_code
HAVING COUNT(id_order) = 1

Upvotes: 0

Related Questions