THIS 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

THIS QUERY

Author

Topic

darbar
Starting Member

16 Posts

Posted - 2013-08-22 : 09:30:22

select p.const_cd, (select c.const_hin from Constituency c where c.const_cd=p.const_cd) "Const_Name",
count(polling_cd) "Total PollingBooth",
(select count(polling_cd) from pollingstation where right(polling_cd,1)='A' group by const_cd HAVING COUNT(*)>=0) "AuxBooth",
count(sector_cd) "TotalSector",
sum(cast(voter_male as int)) + sum(cast(voter_female as int)) as "TotalVoter",
sum(cast(voter_male as int)) TotalMaleVoter, sum(cast(voter_female as int)) "TotalFemaleVoter",
sum(cast(service_male as int)) + sum(cast(service_female as int)) as "TotalServiceVoter",
sum(cast(service_male as int)) Service_male, sum(cast(service_female as int)) Service_female,
sum(cast(voter_male as int)) + sum(cast(voter_female as int)) + sum(cast(service_male as int)) + sum(cast(service_female as int)) "TotalVoters",
sum(cast(epic_holder as int)) "EPICHOLDER", sum(cast(missing_voter as int)) "MissingVoters",
ROUND((sum(cast(epic_holder as int))/cast(sum(cast(voter_male as int)) + sum(cast(voter_female as int)) + sum(cast(service_male as int)) + sum(cast(service_female as int)) as FLOAT))*100,2) AS "EPICPERT"
from PollingStation p, Constituency c
where c.const_cd=p.const_cd
group by p.const_cd
order by p.const_cd

What is wrong in this query:
(select count(polling_cd) from pollingstation where right(polling_cd,1)='A' group by const_cd HAVING COUNT(*)>=0) "AuxBooth",

output
I am getting 1 in all the records instead of a record in which is polling_cd is having A at the end. Please me.

Thanks in advance.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-22 : 09:42:42

select p.const_cd, (select c.const_hin from Constituency c where c.const_cd=p.const_cd) "Const_Name",
count(polling_cd) "Total PollingBooth",
(select count(polling_cd) from pollingstation where right(polling_cd,1)='A' AND p.const_cd = const_cd
group by const_cd HAVING COUNT(*)>=0) "AuxBooth",
count(sector_cd) "TotalSector",
sum(cast(voter_male as int)) + sum(cast(voter_female as int)) as "TotalVoter",
sum(cast(voter_male as int)) TotalMaleVoter, sum(cast(voter_female as int)) "TotalFemaleVoter",
sum(cast(service_male as int)) + sum(cast(service_female as int)) as "TotalServiceVoter",
sum(cast(service_male as int)) Service_male, sum(cast(service_female as int)) Service_female,
sum(cast(voter_male as int)) + sum(cast(voter_female as int)) + sum(cast(service_male as int)) + sum(cast(service_female as int)) "TotalVoters",
sum(cast(epic_holder as int)) "EPICHOLDER", sum(cast(missing_voter as int)) "MissingVoters",
ROUND((sum(cast(epic_holder as int))/cast(sum(cast(voter_male as int)) + sum(cast(voter_female as int)) + sum(cast(service_male as int)) + sum(cast(service_female as int)) as FLOAT))*100,2) AS "EPICPERT"
from PollingStation p, Constituency c
where c.const_cd=p.const_cd
group by p.const_cd
order by p.const_cd

You might get "More than one row returned in the sub query" error.....
I have added one condition the sub query... check with that and let us know the result....
If not working, post us the exact error message

--
Chandu

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-22 : 09:43:01

Try this?

SELECT  p.const_cd ,
        ( SELECT    c.const_hin
          FROM      Constituency c
          WHERE     c.const_cd = p.const_cd
        ) "Const_Name" ,
        SUM (CASE WHEN polling_cd LIKE 'A%' THEN 1 ELSE 0 END) AS "AuxBooth",
        --COUNT(polling_cd) "Total PollingBooth" ,
        --( SELECT    COUNT(polling_cd)
        --  FROM      pollingstation
        --  WHERE     RIGHT(polling_cd, 1) = 'A'
        --  GROUP BY  const_cd
        --  HAVING    COUNT(*) >= 0
        --) "AuxBooth" ,
        COUNT(sector_cd) "TotalSector" ,
        SUM(CAST(voter_male AS INT)) + SUM(CAST(voter_female AS INT)) AS "TotalVoter" ,
        SUM(CAST(voter_male AS INT)) TotalMaleVoter ,
        SUM(CAST(voter_female AS INT)) "TotalFemaleVoter" ,
        SUM(CAST(service_male AS INT)) + SUM(CAST(service_female AS INT)) AS "TotalServiceVoter" ,
        SUM(CAST(service_male AS INT)) Service_male ,
        SUM(CAST(service_female AS INT)) Service_female ,
        SUM(CAST(voter_male AS INT)) + SUM(CAST(voter_female AS INT))
        + SUM(CAST(service_male AS INT)) + SUM(CAST(service_female AS INT)) "TotalVoters" ,
        SUM(CAST(epic_holder AS INT)) "EPICHOLDER" ,
        SUM(CAST(missing_voter AS INT)) "MissingVoters" ,
        ROUND(( SUM(CAST(epic_holder AS INT))
                / CAST(SUM(CAST(voter_male AS INT))
                + SUM(CAST(voter_female AS INT))
                + SUM(CAST(service_male AS INT))
                + SUM(CAST(service_female AS INT)) AS FLOAT) ) * 100, 2) AS "EPICPERT"
FROM    PollingStation p ,
        Constituency c
WHERE   c.const_cd = p.const_cd
GROUP BY p.const_cd
ORDER BY p.const_cd

darbar
Starting Member

16 Posts

Posted - 2013-08-22 : 09:50:15

Thanks James Its working fine.

darbar
Starting Member

16 Posts

Posted - 2013-08-22 : 12:26:27

Thanks James, 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