CODEWITHSUNDEEP
t-sqlcountsql-server-2008-r2case
Albert Laure
Albert Laure

Reputation: 1722

Union Statement that can be changed to Case statement

Good Day Everyone!

well i have this kind of code and it kinda ugly, a friend of mine told me i can implement Case Statements in here, but i do not know how or how would i implement, the code is long so if you could just help me to optimize my code i would appreciate it greatly!

PS. please be gentle to me, im new in T-sql :) Thank yoU!

SELECT 
         SUM(CYJEWELRY) 'CY_Jewelry'
        ,SUM(CYAPPLICANCE) 'CY_Appliance'
        ,SUM(CYCELLPHONE) 'CY_Cellphone'
        ,SUM(PYJEWELRY) 'PY_Jewelry'
        ,SUM(PYAPPLIANCE) 'PY_Appliance'
        ,SUM(PYCELLPHONE) 'PY_Cellphone'
    FROM

        (   

            ---TOTAL NUNG A FORMAT 0,0,0,0,0,0
            --------------CURRENT YEAR JEWELRY
            SELECT COUNT (*) AS CYJEWELRY,0 AS CYAPPLICANCE,0 AS CYCELLPHONE,0 AS PYJEWELRY,0 AS PYAPPLIANCE,0 AS PYCELLPHONE
            FROM #TEMPTABLE1
            WHERE (fld_StorageGroupID  >= 3 and fld_StorageGroupID <= 14)

        UNION
            -----------CURRENT YEAR APPLIANCE

            SELECT 0,COUNT(*),0,0,0,0
            FROM #TEMPTABLE1
            WHERE fld_StorageGroupID = 1

        UNION
            ------------CURRENT YEAR CELLPHONE

            SELECT 0,0,COUNT(*),0,0,0
            FROM #TEMPTABLE1
            WHERE fld_StorageGroupID = 2

        UNION
            ---------------LAST YEAR JEWELRY
            SELECT  0,0,0,COUNT(*),0,0
            FROM #TEMPTABLE2
            WHERE (fld_StorageGroupID  >= 3 and fld_StorageGroupID <= 14)

        UNION
            -----------------------LAST YEAR APPLIANCE
            SELECT 0,0,0,0,COUNT (*),0
            FROM #TEMPTABLE2
            WHERE fld_StorageGroupID = 1

        UNION
            -------------------------LAST YEAR CELLPHONE
            SELECT 0,0,0,0,0,COUNT(*)
            FROM #TEMPTABLE2
            WHERE fld_StorageGroupID = 2
        )A

Upvotes: 0

Views: 45

Answers (1)

Kaf
Kaf

Reputation: 33839

Assuming your data is bit like this Sql Fiddle Example, try this for the sub query using SUM() and CASE.

SELECT SUM(CASE WHEN fld_StorageGroupID  >= 3 and fld_StorageGroupID <= 14 ELSE 0 END) Col1And4,
       SUM(CASE WHEN fld_StorageGroupID = 1 THEN 1 ELSE 0 END)  Col2And5,
       SUM(CASE WHEN fld_StorageGroupID = 2 THEN 1 ELSE 0 END)  Col3And6
FROM #TEMPTABLE1
GROUP BY fld_StorageGroupID

Since you are applying the same filter for last 3 columns in the subquery, I have done only first 3 columns here.

EDIT:

I think this is better than above (Note: no need to use SUM() in the main query).

Fiddle Example with data

select col1_4 CY_Jewelry,
       col2_5 CY_Appliance,
       col3_6 CY_Cellphone,
       col1_4 PY_Jewelry,
       col2_5 PY_Appliance,
       col3_6 PY_Cellphone
from (
select sum(case when id>= 3 and id <= 14 then 1 else 0 end) col1_4,
       sum(case when id = 2 then 1 else 0 end) col2_5,
       sum(case when id = 3 then 1 else 0 end) col3_6
from t
--group by id
) X

Upvotes: 1

Related Questions