join tables with nested select count and group by

Tags:
Database programming
JOIN statement
SELECT statement
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

Answer Wiki

Thanks. We'll let you know when a new response is added.

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

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Denny Cherry
    What result are you getting, and what result are you expecting?
    66,185 pointsBadges:
    report
  • Denny Cherry
    Check out my SQL Server blog "SQL Server with Mr Denny" for more SQL Server information.
    66,185 pointsBadges:
    report
  • Jaideep Khanduja
    yes, what is the exact result you are expecting is important to know.
    9,285 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following