CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
yashpal
yashpal

Reputation: 336

Best way to get Count as field in Sql select statement

Best way to get Count as field in Sql select statement

I have 2 tables: Person and Orders

Person 

 Id            Name            Age
 1            name1           1
 2            name2           2

Order 

Id            Amount           PersonId
 1              30               1
2              40               2
3              30               2
4              40               2
5              30               1
6              40               2
7              30               1
8              40               2

And i want users details with total number of orders , So for this purpose I have 2 solutions:

 1. select p.Name,p.Age,(select count(1) form orders o where o.personId= p.Id as       cntVal 
    from Person p

 2. select p.Name,p.Age,cntVal 
    from Person p
    inner join (select personId,count(1) as cntVal from orders o group by PersonId) cnt
     on cnt.personId=p.Id

We have around 200K records in Person and 15K in Order table. I wanted to know which one is better approach ? Or you can suggest me a faster query

Upvotes: 0

Views: 777

Answers (3)

podiluska
podiluska

Reputation: 51494

This is another alternative - it would be my first choice.

select p.Name,p.Age, count(orderID)
from 
    Person p 
inner join 
    Orders o
        on p.Id = o.personId
group by p.id, p.Name, p.Age

If you want people who haven't placed an order also counted, change the inner join to a left join

Upvotes: 1

Randy Minder
Randy Minder

Reputation: 48402

If your only concern is efficiency and speed, I would suggest you use SQL Profiler and see which option generates the fastest results with the fewest amount of reads and resources consumed.

You also have another choice:

Select  
   p.Name, 
   p.Age, 
   Count(*) as OrderCount
From  Person p 
   Join Orders o on p.PersonId = o.PersonId
Group By p.Name, p.Age

Test all of these and see which is best.

Upvotes: 1

paul
paul

Reputation: 22001

am I missing something?

select  p.name, p.age, count(o.OrderId)
from    Person p join Orders o on p.PersonId = o.PersonId
group by p.name, p.age

Upvotes: 2

Related Questions