CODEWITHSUNDEEP
sqlweighted-average
qwer
qwer

Reputation: 227

SQL weighted average of by dates with missing rows

I have the following table: 

    Date | Flag|  Val   |  Weight
      1  |  A  |    5   |    2    
      1  |  B  |    5   |    2    
      2  |  A  |    10  |    1     
      3  |  A  |    10  |    1
      3  |  B  |    10  |    1

If I do SUM( val * weight ) / SUM( weight ) grouped by the Flag column, then I will get A: 7.5, B: 6.67. However, this is because the second date has no entry for B. I want to treat this as a zero value. So really I should get A: 7.5, B: 5.

How can I do this is SQL without modifying the table and adding a zero row for B?

Upvotes: 0

Views: 175

Answers (2)

Rafael Delboni
Rafael Delboni

Reputation: 857

Based on Brad answer

You can do something like this:

CREATE TABLE tbValues (`Date` int, `Flag` char, `Val` float, `Weight` float);

INSERT INTO tbValues
    SELECT 1, 'A',  5, 2 UNION
    SELECT 1, 'B',  5, 2 UNION
    SELECT 2, 'A', 10, 1 UNION
    SELECT 3, 'A', 10, 1 UNION
    SELECT 3, 'B', 10, 1 ;

CREATE TABLE tbTemp
as (SELECT * FROM (SELECT DISTINCT Date, Weight From tbValues) t1, (SELECT DISTINCT Flag From tbValues) t2);

And then:

SELECT 
    SUM( IFNULL(V.Val,0) * IFNULL(T.Weight,0) ) / SUM( T.Weight ) 
FROM tbValues V
RIGHT JOIN tbTemp T
    ON V.Date = T.Date
    AND V.Flag = T.Flag
GROUP BY
    T.Flag;

An live example here: SqlFiddle

Upvotes: 0

Brad Ruderman
Brad Ruderman

Reputation: 2183

You need to create a frame table. So you have for every date a flag record. Something like 

SELECT * FROM (SELECT DISTINCT Date From Table) t1, (SELECT DISTINCT Flag From Table) t2;

Then you can left join with a COALESCE(0) and when you group by you can use count to get the number of records.

Upvotes: 1

Related Questions