CODEWITHSUNDEEP
sql
whytheq
whytheq

Reputation: 35605

Inserting zero-valued rows

I have the following structure. All I want to achieve is that if for some reason one of the categories does not exist then rather than there being no row for that category there should be a row with zeros for x/y/z.

The following seems a bit convoluted - what is the correct way to achieve this? 

TRUNCATE TABLE dbo.xxx;
INSERT INTO dbo.xxx
    values
    ('cat_1',0.0,0.0,0.0),
    ('cat_2',0.0,0.0,0.0),
    ('cat_3',0.0,0.0,0.0),
    ('cat_4',0.0,0.0,0.0);


--:: category 1
INSERT INTO dbo.xxx
SELECT  Category = 'cat_1',
        x       = SUM(x),
        y       = SUM(y),
        z       = SUM(z)    
FROM    dbo.yyy x
WHERE   DateKey >= period1;

--:: category 2
INSERT INTO dbo.xxx
SELECT  Category = 'cat_2',
        x       = SUM(x),
        y       = SUM(y),
        z       = SUM(z)    
FROM    dbo.yyy x
WHERE   DateKey >= period2;

--:: category 3
INSERT INTO dbo.xxx
SELECT  Category = 'cat_3',
        x       = SUM(x),
        y       = SUM(y),
        z       = SUM(z)    
FROM    dbo.yyy x
WHERE   DateKey >= period3;

--:: category 4
INSERT INTO dbo.xxx
SELECT  Category = 'cat_4',
        x       = SUM(x),
        y       = SUM(y),
        z       = SUM(z)    
FROM        dbo.yyy x
WHERE   DateKey >= period4;

SELECT  Category,
        x       = SUM(x),
        y       = SUM(y),
        z       = SUM(z)
INTO    #temp
FROM    dbo.xxx
GROUP BY Category;

TRUNCATE TABLE dbo.xxx;

INSERT INTO dbo.xxx
SELECT * FROM #temp;

Upvotes: 0

Views: 270

Answers (1)

Trinimon
Trinimon

Reputation: 13967

You can use COALESCE or NVL to define an alternative to NULL

--:: category 1
INSERT INTO dbo.xxx
SELECT  Category = 'cat_1',
        x       = COALESCE (SUM(x),0),
        y       = COALESCE (SUM(y),0),
        z       = COALESCE (SUM(z),0)    
FROM    dbo.yyy x
WHERE   DateKey >= period1;

Upvotes: 1

Related Questions