Unable to understand the SQL code

Question

I am finding it difficult to understand the code below. Can you give me a logical flow of how the code works ?

The question - suppose you want to display the total number of orders placed by every customer in your Customers table. Orders are stored in the Orders table along with the appropriate customer ID.

The steps -

retrieve the list of customers from the customers table.
for each customer retrieved, count the number of associated orders in the Orders table.

The solution -

SELECT cust_name, cust_state, 
    (SELECT COUNT(*)
        FROM Orders
        WHERE Orders.cust_id = Customers.cust_id
    )
AS order_nos
FROM Customers
ORDER BY cust_name

I am unable to understand the count(*) part inside the brackets. Please help me to figure it out.

Thanks.

How does this part work - WHERE Orders.cust_id = Customers.cust_id Can you give me the logic behind it ? — YourDataInsecure, Jun 10 '12 at 08:36

McGarnagle · Answer 1 · 2012-06-10T08:58:38.720

0

It's just counting the number of rows. In this case, that's the number of orders per customer. The alternative COUNT(column_name) gives you the number of rows where column_name isn't null.

Equivalent without the sub-query (using a group instead):

SELECT cust_name, cust_state, COUNT(orders.cust_id) order_nos
FROM Customers
    LEFT OUTER JOIN Orders on Customers.cust_id = Orders.cust_id
GROUP BY cust_name, cust_state
ORDER BY cust_name

edited Jun 10 '12 at 08:58

answered Jun 10 '12 at 08:24

McGarnagle

101,349
31
229
260

Can you give me the code to do the same thing, but without using sub queries ? – YourDataInsecure Jun 10 '12 at 08:25
your result: Fun4All AZ 1 Fun4All IN 1 Kids Place OH 1 The Toy Store IL 1 Village Toys MI 2 ________________________________________________________________ The result given by book: Fun4All IN 1 Fun4All AZ 1 Kids Place OH 0 The Toy Store IL 1 Village Toys MI 2 – YourDataInsecure Jun 10 '12 at 08:39
Your example is not the same as the original. It will return the value 1 for customers without an order. You need to use `count(orders.cust_id)` to get the count of orders per customer. – Jun 10 '12 at 08:40
reentering the rows i got using given answer and my text book's answer. your result: (Fun4All, AZ, 1)--(Fun4All,IN , 1) --(Kids Place,OH,1) --(The Toy Store,IL,1) --(Village Toys,MI,2) The result given by book: (Fun4All,IN,1)--(Fun4All,AZ,1)--(Kids Place,OH,0)--(The Toy Store,IL,1)--(Village Toys,MI,2) – YourDataInsecure Jun 10 '12 at 08:45
why is there no formatting for comments ? Look at the mess created by the above table. – YourDataInsecure Jun 10 '12 at 08:46
@user1447001 so I guess Kids Place in OH has no orders? I updated my answer per a_horse_with_no_name's suggestion-- I think this should give you the right answer. Count(*) counts all rows, but count(Orders.cust_id) counts only those rows that have an order. – McGarnagle Jun 10 '12 at 09:01

score 0 · Answer 2 · answered Jun 10 '12 at 18:14

It's called a correlated subquery. Essentially, for each customer, you're going to get a count of how many orders that customer has. The "magic" is in the WHERE Orders.cust_id = Customers.cust_id clause in the subquery. That's the part that correlates this result to your main query. It's saying "take the cust_id from the main query and now find the count of orders where the cust_id is that cust_id".

Unable to understand the SQL code

2 Answers2