CODEWITHSUNDEEP
sql
emcee22
emcee22

Reputation: 1889

SQL query select client that has the most orders placed

I want to select the client that has the most orders placed:

I have 2 tables :

CREATE TABLE customers(
customerid INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
NAME CHAR(50) NOT NULL,
address CHAR(100) NOT NULL,
city CHAR(30)NOT NULL
);


CREATE TABLE orders(
orderid INT UNSIGNED NOT NULL,
customerid INT UNSIGNED NOT NULL,
);

This is what i am trying to do: 

SELECT customers.customerid, customers.name,orders.orderid, COUNT(customerid) 
AS CostumerCount 
FROM customers 
INNER JOIN orders 
ON customers.customerid=orders.orderid;

How can i make this?

Upvotes: 2

Views: 10691

Answers (6)

Adil KACHBAT
Adil KACHBAT

Reputation: 1

I have edited the first answer because if you have more clients with the same number of orders you will only get the first one!

SELECT 
    customers.customerid, 
    customers.name,
    COUNT(orders.orderid) AS Orderscount
FROM customers 
INNER JOIN orders ON customers.customerid = orders.customerid 
GROUP BY customers.customerid, 
         customers.name
HAVING Orderscount =  (       
    SELECT COUNT(id) AS Orderscount
    FROM orders
    GROUP BY customerid
    ORDER BY Orderscount DESC
    LIMIT 1
)

Upvotes: 0

Shailen
Shailen

Reputation: 1

SELECT * FROM Customers WHERE cno = (
    SELECT cno FROM (
        SELECT count(*) as ordCount, cno 
        FROM ORDERS
        GROUP BY cno HAVING ordCount >= (
            SELECT max(ordCount) 
            FROM ( 
                SELECT count(*) as ordCount, cno 
                FROM ORDERS 
                GROUP BY cno
                )
            )
        )
    )

Upvotes: 0

Kaf
Kaf

Reputation: 33829

For SQL SERVER;

SELECT TOP (1) * FROM (
    SELECT customers.customerid, customers.name, COUNT(*) AS CostumerCount 
    FROM customers INNER JOIN orders 
               ON customers.customerid=orders.customerid
    GROUP BY customers.customerid, customers.name 
) A
ORDER BY CostumerCount DESC

Upvotes: 1

Nikola
Nikola

Reputation: 131

SELECT customers.customerid, customers.name, 
       orders.orderid, COUNT( orders.customerid ) AS CostumerCount
FROM customers
INNER JOIN orders ON customers.customerid = orders.customerid

Upvotes: 1

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79949

Since it is not necessarily for you what RDBMS you are using, then let me assume that you are using MySQL, and you are looking for the customer that has the highest orders count:

SELECT 
  customers.customerid, 
  customers.name,
  COUNT(orders.orderid) AS Orderscount
FROM customers 
INNER JOIN orders ON customers.customerid = orders.customerid 
GROUP BY customers.customerid, 
         customers.name
ORDER BY Orderscount DESC
LIMIT 1;

Upvotes: 3

echo_Me
echo_Me

Reputation: 37233

your create table contain wrong syntax

try this

   CREATE TABLE customers(
   customerid INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
   NAME CHAR(50) NOT NULL,
   address CHAR(100) NOT NULL,
   city CHAR(30)NOT NULL
   );


  CREATE TABLE orders(
  orderid INT UNSIGNED NOT NULL,
  customerid INT UNSIGNED NOT NULL 
  );                               ^----remove comma ',' from here

and your query works good just replace COUNT(customerid) by COUNT(customers.customerid)

try it here 

  SELECT customers.customerid, customers.name,orders.orderid, COUNT(customers.customerid) 
   AS CostumerCount 
   FROM customers 
   INNER JOIN orders 
  ON customers.customerid=orders.orderid;

Upvotes: 1

Related Questions