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.
x
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;