Count of related records??

[hartley_aaron]

Hi,

I was wondering if it was possible to build a query that will include a
column that will provide a count related records from another table.
Although there is a way to achieve this through programming in the
front end, I would like to know if it possible to achieve the same
thing through a SQL statement alone.

For example, say you have two related tables, Invoices and
InvoiceItems. InvoiceID is the primary key of Invoices.

Invoices table

InvoiceID PO_Num CompanyID
-------------------------------
1 37989 3
2 87302 4
3 78942 3

InvoiceItems table

ItemID InvoiceID PartNo Qty
---------------------------------------
1 1 ABA 3
2 1 ASLKDJ 2
3 1 9LF 8
4 2 IEPOW 18
5 2 EIWPD 3
6 2 DSSIO 1
7 2 EIWP 5
8 2 DC93 4
9 3 85LS0 8
Then a query that has the Invoices table plus a count of InvoiceItems
for each InvoiceID would generate this:

InvoiceID PO_Num CompanyID ItemCount
-------------------------------------------------
1 37989 3 3
2 87302 4 5
3 78942 3 1

Does anyone have any ideas how this would be done?

Thank you.

[David Portas]

SELECT I.invoiceid, I.po_num, I.companyid,
COALECSE(T.cnt,0) AS itemcount
FROM Invoices AS I
LEFT JOIN
(SELECT invoiceid, COUNT(*) AS cnt
FROM InvoiceItems
GROUP BY invoiceid) AS T
ON I.invoiceid = T.invoiceid

--
David Portas
SQL Server MVP
--