CODEWITHSUNDEEP
sql
Luke West
Luke West

Reputation: 104

(Simple?) SQL Query: Display all customer information for customers with 2+ orders

I'm doing practice exam material for a distance education course. I have the following three relations (simplified here):

salesperson(emp#, name, salary)
order(order#, cust#, emp#, total)
customer(cust#, name, city)

I'm stuck on a pair of SQL queries.

  1. Display all customer info for customers with at least 1 order.
SELECT * FROM customer
  INNER JOIN order ON order.cust# = customer.cust#
  GROUP BY cust#;
  1. Display all customer info for customers with at least 2 orders.
SELECT cust#, name, city, industry-type FROM customer
  INNER JOIN order ON order.cust# = customer.cust#
  GROUP BY cust#
  HAVING COUNT(cust#) > 2;

I realize these are misguided attempts resulting from a poor understanding of SQL, but I've spent a ton of time on W3School's SQL Query example tool (https://www.w3schools.com/sql/trysql.asp?filename=trysql_select_where) without getting anywhere, and I finally need some "real" help.

Upvotes: 0

Views: 3462

Answers (2)

Sumit Sharma
Sumit Sharma

Reputation: 34

You can change table names according to your DB. Following queries you can directly run in W3Schools

  1. Display all customer info for customers with at least 1 order.

    SELECT * FROM customers as cust JOIN orders as o ON o.customerid = cust.customerid GROUP BY o.customerid;

  2. Display all customer info for customers with at least 2 orders.

    SELECT * FROM customers as cust JOIN orders as O ON O.CustomerID = cust.CustomerID GROUP BY cust.CustomerID HAVING COUNT(cust.CustomerID) > 2;

Upvotes: 0

D-Shih
D-Shih

Reputation: 46249

You can try to use subquery to get count by cust# then do inner join to make it.

SELECT c.*
FROM (
    SELECT cust# , COUNT(*) cnt
    FROM order 
    GROUP BY cust#
) o INNER JOIN customer c ON c.cust# = o.cust#
WHERE o.cnt > 2

Upvotes: 1

Related Questions