Sadia Rashid Sadia Rashid - 3 years ago 401
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
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 ProductsCTE


FROM ProductsCTE

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