Skip to content
Advertisement

How to union all, but separate the columns in mySQL

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;
User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement