Integrating Query with Grouped subQuery

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Integrating Query with Grouped subQuery

Author

Topic

mikebird
Aged Yak Warrior

529 Posts

Posted - 2009-06-03 : 09:18:07

This is a working query:

SELECT --distinct
Centre_Name, ntwrk_cd, det.det_quantity, prc.pr_sort_key, s.stk_sort_key, cp.CNPDateCreated, can.CANCandidateRef
FROM asdandat.dbo.tbl_centre c
INNER JOIN asdandat.dbo.tbl_net_centre nc ON c.centre_id = nc.centre_ID
inner join asdandat.dbo.tbl_candidates can on can.canCentreID = nc.Centre_ID -- error joining on centreNo
inner join asdandat.dbo.tbl_candidatesprogrammes cp on can.CANCandidateID = cp.CNPCandidateID
inner join asdandat.dbo.tbl_programmes p on p.PRGProgrammeCode = cp.CNPProgrammeCode
inner join financetemplate.dbo.sl_accounts sla on c.centre_no = sla.cucode
inner join financetemplate.dbo.sl_pl_nl_detail det on det.det_account = sla.cucode
left join financetemplate.dbo.prc_price_recs prc on prc.prcode = det.det_price_code
left join financetemplate.dbo.stk_stock s on s.stkcode = det_stock_code
WHERE c.Reg_Status = 'R'
and (prc.pr_sort_key = 'CANDIDATE FEES'
or s. stk_sort_key in ('STUDENT BOOKS','RESOURCE BOOKLETS'))
and det.det_quantity > 5 -- I think this is better, rather than aggregating on it
and det_year in ('C','L')

How do I get the grouped results from this part to go together and end up with one selection? Perhaps I need to UNION them but I don't think so. I understand UNIONS and grouping, but once something is grouped, it doesn't fit with anything else, so what's the point?

(select cp.CNPDateCreated, count(cp.CNPDateCreated)
from asdandat.dbo.tbl_centre c
INNER JOIN asdandat.dbo.tbl_net_centre nc ON c.centre_id = nc.centre_ID
inner join asdandat.dbo.tbl_candidates can on can.canCentreID = nc.Centre_ID
inner join asdandat.dbo.tbl_candidatesprogrammes cp on can.CANCandidateID = cp.CNPCandidateID
inner join asdandat.dbo.tbl_programmes p on p.PRGProgrammeCode = cp.CNPProgrammeCode
where c.Reg_Status = 'R'
group by cp.CNPDateCreated
having count(cp.CNPDateCreated)>5)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-03 : 13:15:57

you need to join the two queries using common field CNPDateCreated i guess seeing your explanation

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources