August 19, 2003 at 6:12 am
I'm trying to pull some fields from a header table and get a count of the matching rows in a details table in one SQL statement. I can do it with temp tables and a Stored Procedure, but it seems like it ought to work in a single statement and I am stubborn enough I want to see it.
What I want would be like this:
Select T1.Field1, T1.Field2, count(Rows in T2 where T2.KeyField matches T1.SomeField) From Headers T1, Details T2
Any takers?
Student of SQL and Golf, Master of Neither
Student of SQL and Golf, Master of Neither
August 19, 2003 at 6:25 am
Try this
sample data from northwind
select a.customerid,count(orderid)
from customers a,orders b
where a.customerid=b.customerid
group by a.customerid
Did it help?
Pay Respect to People on your way up. For you will meet the same People on your way down.
He who knows others is learned but the wise one is one who knows himself.
August 19, 2003 at 6:36 am
Your example works (brokenrulz), but it's not the whole answer. It only works for fields from the header table that can be in a group by. I want to be able to pull multiple Fields from the Header table and include the record count from the details table. I want a result set like this:
ID Name CreditLimit NumItemsInDetail
1 Bob 100 5
3 Martha 50 7
Student of SQL and Golf, Master of Neither
Student of SQL and Golf, Master of Neither
August 19, 2003 at 8:56 am
Other solution
SELECT C.*, O.NrOfRecords
FROM Customers C
LEFT OUTER JOIN
(SELECT CustomerID,
COUNT(*) AS NrOfRecords
FROM Orders
GROUP BY CustomerID) O
ON C.CustomerID = O.CustomerID
August 19, 2003 at 9:19 am
Excellent answer. Works perfectly in my initial test. I've never used the Join in that manner, can't see it shown in BOL (v 7) anywhere, but it works great and opens up a ton of possibilities. Instead of specifying a table for the right half of the join, you're specifying a calculated table. Probably quite common, I just hadn't needed it.
Thanks again,
Student of SQL and Golf, Master of Neither
Student of SQL and Golf, Master of Neither
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply