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

Complex relationship

Due to choices beyond my control I am faced with a tough bit of SQL to compose.

I have four tables. Let us callt hem A, B, C & D.

Table B has had it's content wholesale copied into table A. Table A is dynamic and changes table B will never change.

Table B has a non explicite (unenforced) but real one to many relationship to C.

[B] --< [C]

This is due to a unique number for each record within each row of B and none, one or many instances of that number within C.

These number DID NOT copy to A from B as A will be useing an autonumber instead.

The relationship between A and B is the presence of something the developers chose to call the SB_Key it is is 99.9% unique with odds of arround 1 in 1000 chance of two identical values in two different fields. However to get SB_Key duplication the records have to be made in the same second in the same table on the same day (etc) so for our purposes they are all different as no user I know can type taht fast.

[A] -- [B] A has a one to one relationship to B

Now we come to table D. D has had the content of C copied into it. C is static and D is dynamic.

Again the SB_Key is the one to one link between the tables.

[C] -- [D] C has a one to one relation ship with D.

So far all of these relationships are known only to the programmers and are not explicit.

Further tables B and C are in another database file altogeather!

[A] -- [B] --< [C] -- [D]

No comes the bit with which I am haveing some trouble.

[A] --< [D] A should have a one to many relationship with D. The relationship of A to D must match the relationship from B to C.

The data is in place and the plan is to "run an update Query" to replace the Foregn Key in table D with the correct autonumber-generated value from table A based on the relationship between B and C.

I can not whoever seem to comeup with the SQL that will do this.

If I have to I can create the SQL dynamicly in VBa code and create VBa functions to go get information.

However there is a lot of data and the more functions the slower the system. It will be run on PCs ranging from Pentium II to 3 Gig Athlon XP and it is vital that the computer not crash or appear to crash and cause the user to press reset (thus corrupting thier data).

Help.Sounds like you want to do this:

update d
set a_id =
( select a_id
from a, b, c
where a.sb_key = b.sb_key
and b.b_id = c.b_id
and c.sb_key = d.sb_key
);

Complex calculation!!

Hi
I have got two tables and have one to many relationship on them.
i ll show you the sample data

Table 1
ID DoctorName Practice Full_Time Cost Expenditure
1 ABC qw yes $100
2 ABD wer no $566
3 ZXA ddf yes $22
........................

table 2
Practices
qw
wer
ffgg
hhjk
ddf
..
The scenario is like this
a doctor can work in more than one practice. and he can be a full time practioner or a part time practitioner

The problem is if a doctor is a full time practioner in 1 service and a part time practitioner in another, 75% of cost goes to first practice and the rest 25% goes to other

if the doctor is non full time in more than one practice, the cost gets distributed equally in all practices.

how shall i do this?
pls HELP!!

Moving to the T-SQL forum.

Complex calculation!!

Hi
I have got two tables and have one to many relationship on them.
i ll show you the sample data

Table 1
ID DoctorName Practice Full_Time Cost Expenditure
1 ABC qw yes $100
2 ABD wer no $566
3 ZXA ddf yes $22
........................

table 2
Practices
qw
wer
ffgg
hhjk
ddf
..
The scenario is like this
a doctor can work in more than one practice. and he can be a full time practioner or a part time practitioner

The problem is if a doctor is a full time practioner in 1 service and a part time practitioner in another, 75% of cost goes to first practice and the rest 25% goes to other

if the doctor is non full time in more than one practice, the cost gets distributed equally in all practices.

how shall i do this?
pls HELP!!

Moving to the T-SQL forum.