OPTIMIZE THE QUERY - SQL Server Forums

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

New to SQL Server Programming

OPTIMIZE THE QUERY

Author

Topic

darbar
Starting Member

16 Posts

Posted - 2013-08-22 : 09:57:17

select const_cd+' '+const_eng as Const_Name,
(select count(polling_cd) as PollingStations
from pollingstation p
where const_cd = (select distinct(p.const_cd) from pollingstation where p.const_cd=c.const_cd)
group by const_cd) as TotalPollingStn ,
(select count(s1) as PollingStations
from pollingstation p
where const_cd = (select distinct(p.const_cd) from pollingstation where p.const_cd=c.const_cd) and s1=1
group by const_cd) as TotalS1,
(select count(s2) as PollingStations
from pollingstation p
where const_cd = (select distinct(p.const_cd) from pollingstation where p.const_cd=c.const_cd) and s2=1
group by const_cd) as TotalS2,
(select count(s3) as PollingStations
from pollingstation p
where const_cd = (select distinct(p.const_cd) from pollingstation where p.const_cd=c.const_cd) and s3=1
group by const_cd) as TotalS3,
(select count(s4) as PollingStations
from pollingstation p
where const_cd = (select distinct(p.const_cd) from pollingstation where p.const_cd=c.const_cd) and s4=1
group by const_cd) as TotalS4,
(select count(s1)+count(s2)+count(s3)+count(s4) as TotalS1_4
from pollingstation p
where const_cd = (select distinct(p.const_cd) from pollingstation where p.const_cd=c.const_cd) and (s1=1 or s2=1 or s3=1 or s4=1)
group by const_cd) as TotalS1_s4,
(select count(distinct(s1))+count(distinct(s2))+count(distinct(s3))+count(distinct(s4)) as TotalS1_4
from pollingstation p
where const_cd = (select distinct(p.const_cd) from pollingstation where p.const_cd=c.const_cd) and (s1=1 or s2=1 or s3=1 or s4=1)
group by const_cd) as BoothEffect
from constituency c
order by const_cd+' '+ const_eng

I am getting my output using this query but I need some compact version of this query. In BoothEffect column, I need summary of s1 to s4 fields i.e., if every column is having 1 value in 9 records, total 36 then I need only 9 in BoothEffect column.

Please help in making this query more compact and thanx in advance.

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2013-08-22 : 11:30:41

I don't understand exactly what your query is surpossed to do, but here's my suggestion:


select c.const_cd+' '+c.const_eng as Const_Name
      ,p.TotalPollingStn
      ,p.TotalS1
      ,p.TotalS2
      ,p.TotalS3
      ,p.TotalS4
      ,p.TotalS1+p.TotalS2+p.TotalS3+p.TotalS4 as TotalS1_S4
      ,sign(p.TotalS1)+sign(p.TotalS2)+sign(p.TotalS3)+sign(p.TotalS4) as BoothEffect
  from constituency as c
       inner join (select const_cd
                         ,count(polling_cd) as TotalPollingStn
                         ,sum(case when s1=1 then 1 else 0 end) as TotalS1
                         ,sum(case when s1=2 then 1 else 0 end) as TotalS2
                         ,sum(case when s1=3 then 1 else 0 end) as TotalS3
                         ,sum(case when s1=4 then 1 else 0 end) as TotalS4
                     from pollingstation
                    group by const_cd
                  ) as p
               on p.const_cd=c.const_cd
 order by const_cd
         ,const_eng

darbar
Starting Member

16 Posts

Posted - 2013-08-22 : 12:24:55

Thanks Sir, Its working fine. Please mail me on pskachhawaha@gmail.com so that I can communicate you directly.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources