What was the total unit sold for item 10 when the average temperature was below the median temperature?
item 10 refers to item_nbr = 10; average temperature below median temperature refers to tavg < avg(tavg); total units refers to Sum(units)
SELECT SUM(T5.units) FROM weather AS T4 INNER JOIN sales_in_weather AS T5 ON T4.`date` = T5.`date` INNER JOIN relation AS T6 ON T5.store_nbr = T6.store_nbr WHERE T5.item_nbr = 10 AND T4.tavg < ( SELECT AVG(T1.tavg) FROM weather AS T1 INNER JOIN sales_in_weather AS T2 ON T1.`date` = T2.`date` INNER JOIN relation AS T3 ON T2.store_nbr = T3.store_nbr WHERE T2.item_nbr = 10 )
CREATE TABLE sales_in_weather
(
    date      DATE,
    store_nbr INTEGER,
    item_nbr  INTEGER,
    units     INTEGER,
    primary key (store_nbr, date, item_nbr)
);
CREATE TABLE weather
(
    station_nbr INTEGER,
    date        DATE,
    tmax        INTEGER,
    tmin        INTEGER,
    tavg        INTEGER,
    depart      INTEGER,
    dewpoint    INTEGER,
    wetbulb     INTEGER,
    heat        INTEGER,
    cool        INTEGER,
    sunrise     TEXT,
    sunset      TEXT,
    codesum     TEXT,
    snowfall    REAL,
    preciptotal REAL,
    stnpressure REAL,
    sealevel    REAL,
    resultspeed REAL,
    resultdir   INTEGER,
    avgspeed    REAL,
    primary key (station_nbr, date)
);
CREATE TABLE relation
(
    store_nbr   INTEGER
            primary key,
    station_nbr INTEGER,
    foreign key (store_nbr) references sales_in_weather(store_nbr),
    foreign key (station_nbr) references weather(station_nbr)
);
