Case stmt returns duplicate result

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

General SQL Server Forums

Database Design and Application Architecture

Case stmt returns duplicate result

Author

Topic

Syima
Starting Member

13 Posts

Posted - 2007-11-28 : 02:37:29

Hi there,

The following is my table whereby i have joined projects table with issue table (this is 1 to many relationship).

I have the following query:

SELECT   
odf.mbb_sector sectorid,

SUM(case when odf.mbb_projecttype = 'lkp_val_appl' then 1 else 0 end) total_appl,
SUM(case when odf.mbb_projecttype = 'lkp_val_infrastructure' then 1 else 0 end) total_infra,
SUM(case when odf.mbb_projecttype = 'lkp_val_eval' then 1 else 0 end) total_eval,
SUM(case when odf.mbb_projecttype = 'lkp_val_subproject' then 1 else 0 end) total_subprj,
SUM(case when odf.mbb_projecttype = 'lkp_val_nonit' then 1 else 0 end) total_nonit,
SUM(case when odf.mbb_projecttype = 'lkp_val_adhocrptdataextract' or
                odf.mbb_projecttype = 'lkp_val_productionproblem' or
                odf.mbb_projecttype = 'lkp_val_maintwoprogchange' then 1 else 0 end) total_others,
COUNT(distinct prj.prid) total_prj

FROM     
PRJ_PROJECTS AS PRJ,
SRM_PROJECTS AS SRM,
ODF_CA_PROJECT AS ODF

LEFT JOIN RIM_RISKS_AND_ISSUES AS RRI ON RRI.pk_id = odf.id

WHERE    
prj.prid = srm.id
AND srm.id = odf.id
AND srm.is_active =1 
AND odf.mbb_projecttype not in ('lkp_val_budget','lkp_val_itpc')
AND odf.mbb_funcunit = 'lkp_val_operation'

GROUP BY 
odf.mbb_sector

which returns me the following result :

.

The problem is at the lkp_val_infosystem where it returns 3 instead of 1 in the total_infra column. How do I correct my case stmt to return the correct no of projects breakdown by different project type? Currently, only the total_prj which returns correct data.

Thanks

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2007-11-28 : 07:08:20

There is no lkp_val_infosystem in the above?!?

Syima
Starting Member

13 Posts

Posted - 2007-11-28 : 19:43:36

Hi thr,

If you noticed in my 1st table, the 'lkp_val_infosystem' for projecttype = 'lkp_val_infrastructure' should return 1 project instead of 3(refer to name). The reason why it returns 3 bcoz it has 3 issueid. However, i only want it to count as 1 project regardless how many issueid it has.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2007-11-29 : 03:36:27

Sorry, I can't download the pictures, can you give some DDL instead?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources