SUM subquery with condition depends on parent query columns returns NULL

Question

everyone!

I'm trying to calc sum of price of deals by each day. What i do:

SET @symbols_set = "A,B,C,D";

DROP TABLE IF EXISTS temp_deals;

CREATE TABLE temp_deals AS SELECT Deal, TimeMsc, Price, VolumeExt, Symbol FROM deals WHERE TimeMsc >= "2019-04-01" AND TimeMsc <= "2019-06-30" AND FIND_IN_SET(Symbol, @symbols_set) > 0;

SELECT
    DATE_FORMAT(TimeMsc, "%d/%m/%Y") AS Date,
    Symbol,
    (SELECT SUM(Price) FROM temp_deals dap WHERE dap.TimeMsc BETWEEN Date AND Date + INTERVAL 1 DAY AND dap.Symbol = Symbol) AS AvgPrice
FROM temp_deals
ORDER BY Date;

DROP TABLE IF EXISTS temp_deals;

But in result i've got NULL in AvgPrice column. I can't understand what i'm doing wrong.

It's look like i can't pass parent query's column to subquery, am i right?

You are using the column Date inside the subquery and the code works? — forpas, Aug 26 '19 at 14:06
@forpas yes, this query executes without any syntax error. But it's result is totally wrong :) — dpleshakov, Aug 26 '19 at 15:21

score 1 · Accepted Answer · answered Aug 26 '19 at 14:01

Qualify your column names. But mostly, don't use a string for comparing dates:

SELECT DATE_FORMAT(d.TimeMsc, '%d/%m/%Y') AS Date,
       d.Symbol,
       (SELECT SUM(dap.Price)
        FROM temp_deals dap
        WHERE dap.TimeMsc >= d.TimeMsc AND
              dap.TimeMsc < d.TimeMsc + INTERVAL 2 DAY AND  -- not sure if you want 1 day or 2 day
              dap.Symbol = d.Symbol
       ) AS AvgPrice
FROM temp_deals d
ORDER BY d.TimeMsc;

SUM subquery with condition depends on parent query columns returns NULL

1 Answers1