SQL weighted average of by dates with missing rows

Question

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?

if that is what you want to do, it isn't called `weighted average` — Vamsi Prabhala, Aug 10 '15 at 17:20
MySQL. Yes its a weighted average but one of the rows is not being included because there is no data for that row. — qwer, Aug 10 '15 at 17:31
Even what you say is done then also it will return the same result as 0 is not going to change anything. — Mahesh, Aug 10 '15 at 17:50

score 1 · Answer 1 · answered Aug 10 '15 at 18:13

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.

score 0 · Answer 2 · edited May 23 '17 at 12:14

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

SQL weighted average of by dates with missing rows

2 Answers2