CODEWITHSUNDEEP
sqlsql-server
Tauseef Hussain
Tauseef Hussain

Reputation: 1079

SQL: Trying to understand IF/ELSE

SELECT CASE r.SourceId
                        WHEN '1' THEN 'ITUNES' 
                        WHEN '2' THEN 'SFR'
                        WHEN '3' THEN 'ORANGE' 
                        ELSE 'Others'
                        END as source 
, CAST(SUM (r.SalesVolume) AS DECIMAL(14, 4) ) AS Volume
, CAST(SUM (r.SalesVolume * r.CustomerPrice) AS DECIMAL(14, 4) ) AS Value 
from Rawdata r 
    INNER JOIN Product p
        ON p.ProductId = r.ProductId
    INNER JOIN Calendar c 
        ON r.DayId = c.DayId
        WHERE c.WeekId BETWEEN (20145227) AND (20155230)
        AND p.ContentFlavor IN ('SD', 'HD')
        AND p.VODEST IN ('VOD','EST')
        AND p.Distributor IN ('M6SND')
        GROUP BY  CASE r.SourceId
            WHEN '1' THEN 'ITUNES' 
            WHEN '2' THEN 'SFR'
            WHEN '3' THEN 'ORANGE' 
            ELSE 'Others'
            END

The result of the above query is:

source  Volume      Value
ITUNES  48316.0000  506067.2600

This result is perfectly OK since my source table RawData doesnt contain any values for SourceId 2 or 3. But what I basically want is the result to look like is:

source  Volume      Value
ITUNES  48316.0000  506067.2600
SFR     0           0
ORANGE  0           0
Others  0           0

If there is no value corresponding to any column parameter then I need it to be 0 I assume this could be done using IF/ELSE but not sure how?

Upvotes: 0

Views: 55

Answers (2)

Senthil
Senthil

Reputation: 189

  1. Create an inline view called "Product_Inline_View", which is like (select 1 as SourceId, 'ITUNES' as source_name union all select 2 as SourceId, 'SFR' as source_name union all select 3 as SourceId, 'ORANGE' as source_name )
  2. Right Join the Product_Inline_view with the Query you have, but without the CASE.
  3. And then do the group by.

Upvotes: 0

mxix
mxix

Reputation: 3659

with the help of a CTE this is a way to do it. (replace the first query with something more dynamic if you want)

with myChoices (choices)
as (
    select
        choices
    from (
        values
        ('ITUNES'),
        ('SFR'),
        ('ORANGE'),
        ('Others')
    ) [ ] (choices)
),
myQuery ([source],[Volume],[Value])
as (
    SELECT CASE r.SourceId
                        WHEN '1' THEN 'ITUNES' 
                        WHEN '2' THEN 'SFR'
                        WHEN '3' THEN 'ORANGE' 
                        ELSE 'Others'
                        END as source 
    , CAST(SUM (r.SalesVolume) AS DECIMAL(14, 4) ) AS Volume
    , CAST(SUM (r.SalesVolume * r.CustomerPrice) AS DECIMAL(14, 4) ) AS Value 
    from Rawdata r 
    INNER JOIN Product p
        ON p.ProductId = r.ProductId
    INNER JOIN Calendar c 
        ON r.DayId = c.DayId
        WHERE c.WeekId BETWEEN (20145227) AND (20155230)
        AND p.ContentFlavor IN ('SD', 'HD')
        AND p.VODEST IN ('VOD','EST')
        AND p.Distributor IN ('M6SND')
        GROUP BY  CASE r.SourceId
            WHEN '1' THEN 'ITUNES' 
            WHEN '2' THEN 'SFR'
            WHEN '3' THEN 'ORANGE' 
            ELSE 'Others'
            END
)
select
    c.choices,
    ISNULL(q.Volume,0)Volume,
    ISNULL(q.Value,0)Value
from myChoices c 
left join myQuery q on
    c.choices = q.[source]

Upvotes: 1

Related Questions