Count of related records?

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.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
--

Count by matching first two letters

Say I have a table of names
How\Can I provide a count for names that have matching first two letters
For instance if my table was this
Name
DA234
DA333
ebEEE
EBddd
EEddd
I would want my output to be
DA 2
EB 2
EE 1
I hope I am communicating my question correctly and thanks for your
consideration.
ScottTry:
select
LEFT (MyCol, 2)
, count (*)
from
MyTable
group by
LEFT (MyCol, 2)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"scott" <scott@.discussions.microsoft.com> wrote in message
news:3ED42509-B63D-4376-8C81-981ED42C9D2D@.microsoft.com...
Say I have a table of names
How\Can I provide a count for names that have matching first two letters
For instance if my table was this
Name
DA234
DA333
ebEEE
EBddd
EEddd
I would want my output to be
DA 2
EB 2
EE 1
I hope I am communicating my question correctly and thanks for your
consideration.
Scott|||Try,
select left(name, 2) as l2_name, count(*) as cnt
from t1
group by left(name, 2)
AMB
"scott" wrote:

> Say I have a table of names
> How\Can I provide a count for names that have matching first two letters
> For instance if my table was this
> Name
> DA234
> DA333
> ebEEE
> EBddd
> EEddd
> I would want my output to be
> DA 2
> EB 2
> EE 1
> I hope I am communicating my question correctly and thanks for your
> consideration.
> Scott|||scott wrote:
> Say I have a table of names
> How\Can I provide a count for names that have matching first two
> letters
> For instance if my table was this
> Name
> DA234
> DA333
> ebEEE
> EBddd
> EEddd
> I would want my output to be
> DA 2
> EB 2
> EE 1
> I hope I am communicating my question correctly and thanks for your
> consideration.
> Scott
SELECT UPPER(LEFT(COL_NAME, 2)), COUNT(*)
FROM TABLE_NAME
GROUP BY UPPER(LEFT(COL_NAME, 2))
David Gugick
Imceda Software
www.imceda.com