using count(*) in a subquery - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

using count(*) in a subquery

Author

Topic

Reprovo
Starting Member

8 Posts

Posted - 2014-01-31 : 02:24:00

Hi.I'm trying to understand how the subquery below works.Why does the count(*) occur for each customer ID when It's inside a subquery.Has It something to do with using two tables ( the orders table inside the subquery and the customer table outside.)

(From Sam's teach yourself SQL In 10 minutes)

INPUT


SELECT cust_name,

cust_state,

(SELECT COUNT(*)

        FROM Orders

        WHERE Orders.cust_id = Customers.cust_id) AS

orders

FROM Customers

ORDER BY cust_name;

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2014-01-31 : 05:27:43

yes

Too old to Rock'n'Roll too young to die.

Reprovo
Starting Member

8 Posts

Posted - 2014-01-31 : 06:50:36

Thanks for breaking It down for me

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2014-01-31 : 06:58:17

The WHERE clause of the inner query is doing the connection to the outer query via cust_id.
So yes, you're right.

Too old to Rock'n'Roll too young to die.

Reprovo
Starting Member

8 Posts

Posted - 2014-01-31 : 07:13:53

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-31 : 08:03:20

Why does the count(*) occur for each customer ID when It's inside a subquery
The subquery gets executed once for each record in the main query. So regardless of whether you use same or different table inside the sunquery it will return you count value per each record returned by your outer select
for example see below case


SELECT cust_name,

cust_state,

(SELECT COUNT(*)

        FROM Customers) AS

TotalCustomerCount

FROM Customers

ORDER BY cust_name;

This will return you the totalcustomercount value along with each record returned by the select statement

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Reprovo
Starting Member

8 Posts

Posted - 2014-07-03 : 11:26:44

A little late to reply ( very late) but thanks for the Info !

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources