complicated use of select and count.

Hi,
I have 2 tables, with a 1-m relationship between them and I am trying
to design a query that seems to beyond what sql can do.
Lets say Table A, has the 1 one side of the relationship and table B
the many. Basically I want a query that returns a list of some or all
entries in Table A (thats the easy bit) and a count of the total
number of records in Table B that meet certain criteria. It seems like
what I want is a where clause for each count clause, rather than a
single where clause for the whole query
TableA has a PrimaryKey of MasterID and TableB has a foreign key to it
also called MasterID
Something like Select TableA.MasterID, COUNT( TableB.* where
TableB.Foo = bar) as 'BarFooCount', COUNT(TableB.* where
TableB.Category = 'Sample') as 'SampleCount', COUNT(TableB.* where
TableB.Category = 'Macro' or TableB.Category = 'Micro') as
'MacroOrMicroCount'
FROM TableA, TableB,
Inner Join blah blah blah;
Basically to get a query back that lists each TableA item, along with
summary information about each TableAs TableB records. It doesnt seem
like I can put where clauses in count clauses so can this be done in
SQL another way?Here's on option, based on Adventureworks database:
SELECT
soh.SalesOrderID
,soh.OrderDate
,(SELECT COUNT(*) FROM Sales.SalesOrderDetail AS sod
WHERE sod.SalesOrderID = soh.SalesOrderID) AS NoOrders
FROM Sales.SalesOrderHeader AS soh
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Oolis Kraprin" <Oolis.Kraprin@.googlemail.com> wrote in message
news:bc3c3456-7d89-4533-8171-91926ada7bf4@.m34g2000hsb.googlegroups.com...
> Hi,
> I have 2 tables, with a 1-m relationship between them and I am trying
> to design a query that seems to beyond what sql can do.
> Lets say Table A, has the 1 one side of the relationship and table B
> the many. Basically I want a query that returns a list of some or all
> entries in Table A (thats the easy bit) and a count of the total
> number of records in Table B that meet certain criteria. It seems like
> what I want is a where clause for each count clause, rather than a
> single where clause for the whole query
> TableA has a PrimaryKey of MasterID and TableB has a foreign key to it
> also called MasterID
> Something like Select TableA.MasterID, COUNT( TableB.* where
> TableB.Foo = bar) as 'BarFooCount', COUNT(TableB.* where
> TableB.Category = 'Sample') as 'SampleCount', COUNT(TableB.* where
> TableB.Category = 'Macro' or TableB.Category = 'Micro') as
> 'MacroOrMicroCount'
> FROM TableA, TableB,
> Inner Join blah blah blah;
> Basically to get a query back that lists each TableA item, along with
> summary information about each TableAs TableB records. It doesnt seem
> like I can put where clauses in count clauses so can this be done in
> SQL another way?|||Excellent, that put me in exactly the right direction!
Thank you very much!
On Jan 28, 1:09 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> Here's on option, based on Adventureworks database:
> SELECT
> soh.SalesOrderID
> ,soh.OrderDate
> ,(SELECT COUNT(*) FROM Sales.SalesOrderDetail AS sod
> WHERE sod.SalesOrderID = soh.SalesOrderID) AS NoOrders
> FROM Sales.SalesOrderHeader AS soh
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> "Oolis Kraprin" <Oolis.Krap...@.googlemail.com> wrote in message
> news:bc3c3456-7d89-4533-8171-91926ada7bf4@.m34g2000hsb.googlegroups.com...
> > Hi,
> > I have 2 tables, with a 1-m relationship between them and I am trying
> > to design a query that seems to beyond what sql can do.
> > Lets say Table A, has the 1 one side of the relationship and table B
> > the many. Basically I want a query that returns a list of some or all
> > entries in Table A (thats the easy bit) and a count of the total
> > number of records in Table B that meet certain criteria. It seems like
> > what I want is a where clause for each count clause, rather than a
> > single where clause for the whole query
> > TableA has a PrimaryKey of MasterID and TableB has a foreign key to it
> > also called MasterID
> > Something like Select TableA.MasterID, COUNT( TableB.* where
> > TableB.Foo = bar) as 'BarFooCount', COUNT(TableB.* where
> > TableB.Category = 'Sample') as 'SampleCount', COUNT(TableB.* where
> > TableB.Category = 'Macro' or TableB.Category = 'Micro') as
> > 'MacroOrMicroCount'
> > FROM TableA, TableB,
> > Inner Join blah blah blah;
> > Basically to get a query back that lists each TableA item, along with
> > summary information about each TableAs TableB records. It doesnt seem
> > like I can put where clauses in count clauses so can this be done in
> > SQL another way?|||On Mon, 28 Jan 2008 01:39:51 -0800 (PST), Oolis Kraprin wrote:
>Hi,
>I have 2 tables, with a 1-m relationship between them and I am trying
>to design a query that seems to beyond what sql can do.
>Lets say Table A, has the 1 one side of the relationship and table B
>the many. Basically I want a query that returns a list of some or all
>entries in Table A (thats the easy bit) and a count of the total
>number of records in Table B that meet certain criteria. It seems like
>what I want is a where clause for each count clause, rather than a
>single where clause for the whole query
>TableA has a PrimaryKey of MasterID and TableB has a foreign key to it
>also called MasterID
>Something like Select TableA.MasterID, COUNT( TableB.* where
>TableB.Foo = bar) as 'BarFooCount', COUNT(TableB.* where
>TableB.Category = 'Sample') as 'SampleCount', COUNT(TableB.* where
>TableB.Category = 'Macro' or TableB.Category = 'Micro') as
>'MacroOrMicroCount'
>FROM TableA, TableB,
>Inner Join blah blah blah;
>Basically to get a query back that lists each TableA item, along with
>summary information about each TableAs TableB records. It doesnt seem
>like I can put where clauses in count clauses so can this be done in
>SQL another way?
Hi Oolis,
Here's a basic outline of what you could use:
SELECT A.MasterID,
SUM(CASE WHEN B.Foo = 'Bar' THEN 1 ELSE 0 END) AS FooCount,
SUM(CASE WHEN B.Foo = 'Sample' THEN 1 ELSE 0 END) AS
SampleCount
FROM TableA AS A
INNER JOIN TableB AS B
ON B.MasterID = A.MasterID
GROUP BY A.MasterID;
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis