The first query returns the data for the last 7 days, and returns 90 in total, while the second query returns data for the previous 7 days, returns 55 in total.
SELECT Market, count(*) AS This_week FROM Buy_orders_SMA7_35_STAGE sma_tw left JOIN SECTOR sec ON sec.Ticker = sma_tw.Name_buy WHERE sma_tw.Date_buy >= date_sub(current_date, INTERVAL 7 day) group by market UNION ALL SELECT Market, count(*) AS Last_week FROM Buy_orders_SMA7_35_STAGE sma_lw left JOIN SECTOR sec ON sec.Ticker = sma_lw.Name_buy WHERE sma_lw.Date_buy >= date_sub(current_date, INTERVAL 14 day) AND sma_lw.Date_buy < date_sub(current_date, INTERVAL 7 day) group by market
Current output is:
Market This_week NULL 70 OMXC25 6 OMXO20GI 8 OMXS30 6 NULL 26 OMXC25 21 OMXO20GI 8
But the output should be:
Market This_week Last_week Change NULL 70 26 169.23% OMXC25 6 21 -71.43% OMXO20GI 8 8 0.00% OMXS30 6 0 100.00%
Advertisement
Answer
You can use conditional aggregation:
SELECT Market, SUM(sma.Date_buy >= date_sub(current_date, INTERVAL 7 day)) AS This_week, SUM(sma.Date_buy >= date_sub(current_date, INTERVAL 14 day) AND sma.Date_buy < date_sub(current_date, INTERVAL 7 day)) as last_week, (1 - SUM(sma.Date_buy >= date_sub(current_date, INTERVAL 7 day)) / SUM(sma.Date_buy >= date_sub(current_date, INTERVAL 14 day) AND sma.Date_buy < date_sub(current_date, INTERVAL 7 day)) ) as ratio FROM Buy_orders_SMA7_35_STAGE sma left JOIN SECTOR sec ON sec.Ticker = sma.Name_buy WHERE sma.Date_buy >= date_sub(current_date, INTERVAL 14 day) GROUP BY market;