Query to return count using group by?

[rrchowdhary]

I am having trouble getting the count of records from tableA

I need to find the LocationId whose count is < 5

here is the Sample Data

TableA
=======
LocationId
1
2
3


TableB
=======
LocationId	DocumentId

1		1000
1		1001
1		1002
1		1003
1		1004

2		1005
2		1006
2		1007
2		1008
2		1009

I need to get the LocationId whose count < 5 and, in this case i should get the LocationId 3 from Table A.

Thanks very much for any help.

 

[gmmastros]

Try this...

Select TableA.LocationId
From   TableA
       Left Join TableB 
         On TableA.LocationId = TableB.LocationId
Group By TableA.LocationId
Having Count(TableB.LocationId) < 5

If this works for you, and you would like me to explain it, just ask.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[rrchowdhary]

Thanks George..

This works perfectly..

 

[gmmastros]

Yeah. I know. What's more interesting to me is.... do you understand it?



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[chrissie1]

Yeah. I know.

I'm having a good effect on you.



Christiaan Baes
Belgium

My Blog

 

[rrchowdhary]

This is what i have written initially and i am sure i understand your query

SELECT Locationid
FROM TableB
WHERE Locationid in
(
SELECT Locationid FROM TableA
)
GROUP BY Locationid
HAVING count(TableA.documentId) < 5

Thanks

 

[gmmastros]

Right. That query didn't work because there are NO records in TableB for LocationId = 3.

Another way you could have written this is...

SELECT Locationid
FROM   TableA
WHERE  Locationid Not In
(
    SELECT Locationid FROM TableB
    Group By LocationId
    Having Count(*) >= 5
)

This subquery method will be considerably slower than the Left Join method that I showed earlier. You may not notice a difference with small tables, but if your tables are large (or will become large) you will see the difference.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[rrchowdhary]

you are right george, that's why i have used the query you have mentioned earlier, as i had to query the DB for nearly 500000 records..