Best way to get Count as field in Sql select statement

Question

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

Should be have no difference in speed (especially if you fix second inner query to group by personId). Additionally, you could use count(*) to improve readability. — Arvo, Sep 07 '12 at 13:51
Arvo - count(1) is better than count(*) for performance reasons — Vishal Bardoloi, Sep 07 '12 at 13:56
@VeeBee: complete rubbish: http://stackoverflow.com/questions/1221559/count-vs-count1/1221649#1221649 — gbn, Sep 07 '12 at 13:57
@VeeBee This is not true. [Please check this answer out](http://stackoverflow.com/questions/1221559/count-vs-count1). — Nikola Markovinović, Sep 07 '12 at 13:58
yashpal - 2 is more efficient than 1, but you can improve 2 still further by using Group By on the outside (see paul's answer below) — Vishal Bardoloi, Sep 07 '12 at 13:58
gbn, Nikola - thanks for the correction on count(1), I was once told by a MySQL DBA that this is more efficient and took that as gospel. Stand corrected. — Vishal Bardoloi, Sep 07 '12 at 14:00
My actual problem is more complex i have several other tables those are involved in query so simply we can not use grouping. For eg Total amount paid by user,count of credit card payment,count of debit card payment and so on. I have around 15 tables in join. — yashpal, Sep 07 '12 at 17:30

score 2 · Answer 1 · answered Sep 07 '12 at 13:50

2

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

answered Sep 07 '12 at 13:50

paul

21,653
1
53
54

podiluska · Answer 2 · 2012-09-07T14:08:06.243

1

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

edited Sep 07 '12 at 14:08

answered Sep 07 '12 at 13:51

podiluska

50,950
7
98
104

The only concern I would have with this approach is if there are many more columns in `person` that the OP is also looking to retrieve. But yes, from the question as asked this looks like the way to go, most everything else is too complex. – David Faber Sep 07 '12 at 14:04
My actual problem is more complex i have several other tables those are involved in query so simply we can not use grouping. For eg Total amount paid by user,count of credit card payment,count of debit card payment and so on. I have around 15 tables in join, so it wont work for me. – yashpal Sep 07 '12 at 17:28
In that case, you'll want to use the subquery outlined in 2, I think. It might be helpful to create a view for it. – David Faber Sep 07 '12 at 20:10

score 1 · Answer 3 · answered Sep 07 '12 at 13:53

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.

Best way to get Count as field in Sql select statement

3 Answers3