Sadia Rashid Sadia Rashid - 8 months ago 122
SQL Question

Same select criteria with multiple where conditions in SQL Server

Is it possible to do something like this when there are same fields required from the table but having different where conditions.

I have tried to do it like this but there is something missing in it that's why its not working properly.

;WITH ProductsCTE (PSC_Score_0_11, PSC_Score_12_18, PSC_Score_19_23,
PSC_Score_24_34, PSC_Score_35_50, PSC_Score_51_100,
Total AS
(
SELECT
SUM(CONVERT(int, PSC_Score_0_11)) AS PSC_Score_0_11,
SUM(CONVERT(int, PSC_Score_12_18)) AS PSC_Score_12_18,
SUM(CONVERT(int, PSC_Score_19_23)) AS PSC_Score_19_23,
SUM(CONVERT(int, PSC_Score_24_34)) AS PSC_Score_24_34,
SUM(CONVERT(int, PSC_Score_35_50)) AS PSC_Score_35_50,
SUM(CONVERT(int, PSC_Score_51_100)) AS PSC_Score_51_100,
(SUM(CONVERT(int, PSC_Score_0_11)) + SUM(CONVERT(int, PSC_Score_12_18)) +
SUM(CONVERT(int, PSC_Score_19_23)) + SUM(CONVERT(int, PSC_Score_24_34)) +
SUM(CONVERT(int, PSC_Score_35_50)) + SUM(CONVERT(int, PSC_Score_51_100))) AS Total
FROM
VillageLevelPscData
)
SELECT *
FROM ProductsCTE
WHERE [DISTRICT_NAME] = 'ABC'

UNION ALL

SELECT *
FROM ProductsCTE
WHERE [DISTRICT_NAME] = 'DEF'


I want to get the result as sum of "PSC_Score_0_11" and others where district = 'ABC' and district = 'DEF' and union all them into one table.

I get an error for this query:


Incorrect syntax near the keyword 'AS'.


I want to get the results like this for each individual district.

enter image description here

Answer Source

I am guessing that you want:

select district_name,
       sum(CONVERT(int,PSC_Score_0_11 )) as PSC_Score_0_11,
       sum(CONVERT(int,PSC_Score_12_18 )) as PSC_Score_12_18,
       sum(CONVERT(int,PSC_Score_19_23 )) as PSC_Score_19_23,
       sum(CONVERT(int,PSC_Score_24_34 )) as PSC_Score_24_34,
       sum(CONVERT(int,PSC_Score_35_50 )) as PSC_Score_35_50,
       sum(CONVERT(int,PSC_Score_51_100 )) as PSC_Score_51_100,
       (SUM(CONVERT(int,PSC_Score_0_11 )) + SUM(CONVERT(int,PSC_Score_12_18 )) + 
SUM(CONVERT(int,PSC_Score_19_23 )) + SUM(CONVERT(int,PSC_Score_24_34 ))
 + SUM(CONVERT(int,PSC_Score_35_50 ))+ SUM(CONVERT(int,PSC_Score_51_100 ))
       ) as Total
from VillageLevelPscData
group by district_name;

The CTE doesn't seem particularly useful.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download