CODEWITHSUNDEEP
sqlsql-server
Sadia
Sadia

Reputation: 181

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

Upvotes: 0

Views: 38

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

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.

Upvotes: 2

Related Questions