join tables with nested select count and group by
hi
i am having three tables : state, agencydetail, parentdetaills
i am running following queries but i am getting wrong result
plz help me out where is the problem
select state.state,count(ag.agencycode) as registag,count(ff.agencycode) as reportag,count(inp.regno) as incpwait,count(interp.regno) as intercpwait from state
left outer join
(select distinct agencycode,state from agencydetail) ag
on
state.state=ag.state
left outer join
(select distinct agencycode from childdetails) ff
on
ag.agencycode=ff.agencycode
left outer join
(select agcode,regno from parentdetails where fagcode='-' and result='W') inp
on
ag.agencycode=inp.agcode
left outer join
(select agcode,regno from parentdetails where fagcode!='-' and result='W') interp
on
ag.agencycode=interp.agcode
group by
state.state
order by
state
thanx in advance
Software/Hardware used:
Software/Hardware used:
ASKED:
July 25, 2008 6:28 AM
UPDATED:
July 29, 2008 4:07 AM
Answer Wiki:
If you want a quick and dirty solution, just move your distincts outside and work with another select:
<pre>
select
state state
, count(registag)
, count(registag)
, sum(incpwait)
, sum(intercpwait)
from
(
select state.state as state
,distinct (ag.agencycode) as registag
,distinct (ff.agencycode) as reportag
,count(inp.regno) as incpwait
,count(interp.regno) as intercpwait
from state
left outer join
(select distinct agencycode,state from agencydetail) ag
on
state.state=ag.state
left outer join
(select distinct agencycode from childdetails) ff
on
ag.agencycode=ff.agencycode
left outer join
(select agcode,regno from parentdetails where fagcode='-' and result='W') inp
on
ag.agencycode=inp.agcode
left outer join
(select agcode,regno from parentdetails where fagcode!='-' and result='W') interp
on
ag.agencycode=interp.agcode
group by
state.state
,distinct (ag.agencycode) as registag
,distinct (ff.agencycode) as reportag
) tmp
group by state
order by
state
</pre>
I didn't tried it out, but it should work. Your outer joins are the problem. I would rewrite the query and put all the last four tables in one join (one relation) and count over that relation.
HTH
To see all answers submitted to the Answer Wiki: View Answer History.
What result are you getting, and what result are you expecting?
Check out my SQL Server blog “SQL Server with Mr Denny” for more SQL Server information.
yes, what is the exact result you are expecting is important to know.