Select orders (this month) and orders (all time) in one query

Databases
1

I want to display order totals in two columns, one column is for sales this month and one column is total sales (all time). Here is the query that works fine for sales this month:

SELECT product_id, name, sum(qty) sold, 
(sum(qty) * price) revenue, price 
from transaction_product 
WHERE date>'$this_month' 
GROUP BY product_id

My failed attempt was to use UNION leaving out the date in the second query:

SELECT product_id, name, sum(qty) sold, 
(sum(qty) * price) revenue, price 
from transaction_product 
WHERE date>'$this_month' 
GROUP BY product_id 
UNION 
(SELECT product_id, name, sum(qty) total_sold, 
(sum(qty) * price) total_revenue, price 
from transaction_product 
GROUP BY product_id)

I don’t get an error but the total_sold and total_revenue values are 0.

Any help much appreciated.

2

Awesome thanks heaps.

I was looking at some other posts and it seemed the UNION wasn’t the way to go.

The price shouldn’t change but I have grouped by product_id,price to be sure.

3

i like how you restricted the query to the current month by passing in a date value

let’s use that same idea but pass in two dates, one for this month and one for this year

SELECT product_id
     , name
     , SUM(qty)         AS sold_this_year
     , SUM(qty) * price AS revenue_this_year
     , SUM(CASE WHEN `date` > [COLOR="blue"]'$this_month'[/COLOR] 
                THEN qty 
                ELSE NULL 
            END )       AS sold_this_month
     , SUM(CASE WHEN `date` > [COLOR="blue"]'$this_month'[/COLOR] 
                THEN qty 
                ELSE NULL 
            END ) * price AS revenue_this_month
     , price 
  FROM transaction_product 
 WHERE `date` > [COLOR="Blue"]'$this_year'[/COLOR] 
GROUP 
    BY product_id

note that if the price changes anywhere during this time, you will get wrong results