Counting items

I want to run a select query and also using that items keys get the count of
the items in the inventory db, kinda like this below but does not Parse
SELECT dbo.Pattern.Pattern_ID, dbo.ProductType.ProdType_ID,
dbo.Manufact_Company.ManuComp_ID, XCount AS
(SELECT Count(ID)
FROM wholesaleinv
WHERE Pattern_ID = dbo.Pattern.Pattern_ID
AND ManuComp_ID = dbo.Manufact_Company.ManuComp_ID AND
Prodtype_ID = dbo.PTC2CLEAN.ProdType_ID)
FROM dbo.Pattern INNER JOIN
dbo.PTC2CLEAN ON dbo.Pattern.Pattern_ID =
dbo.PTC2CLEAN.Pattern_ID INNER JOIN
dbo.ProductType ON dbo.PTC2CLEAN.ProdType_ID =
dbo.ProductType.ProdType_ID INNER JOIN
dbo.Manufact_Company ON dbo.PTC2CLEAN.ManuComp_ID =
dbo.Manufact_Company.ManuComp_IDPlease post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"TdarTdar" <TdarTdar@.discussions.microsoft.com> wrote in message
news:231CE73E-0716-46C4-A4C4-B7C688023022@.microsoft.com...
>I want to run a select query and also using that items keys get the count
>of
> the items in the inventory db, kinda like this below but does not Parse
> SELECT dbo.Pattern.Pattern_ID, dbo.ProductType.ProdType_ID,
> dbo.Manufact_Company.ManuComp_ID, XCount AS
> (SELECT Count(ID)
> FROM wholesaleinv
> WHERE Pattern_ID = dbo.Pattern.Pattern_ID
> AND ManuComp_ID = dbo.Manufact_Company.ManuComp_ID AND
> Prodtype_ID = dbo.PTC2CLEAN.ProdType_ID)
> FROM dbo.Pattern INNER JOIN
> dbo.PTC2CLEAN ON dbo.Pattern.Pattern_ID =
> dbo.PTC2CLEAN.Pattern_ID INNER JOIN
> dbo.ProductType ON dbo.PTC2CLEAN.ProdType_ID =
> dbo.ProductType.ProdType_ID INNER JOIN
> dbo.Manufact_Company ON dbo.PTC2CLEAN.ManuComp_ID =
> dbo.Manufact_Company.ManuComp_ID|||A quick glance reveals a peculiarity - "..., XCount AS (select..." is wrong.
Replace it with "(select ...) as XCount".
Any other help will be available if you share your DDL with us.
ML