Combine 2 queries together

I am struggling to work out combining a query that should give me 3 columns of Month, total_sold_products and drinks_sold_products

Query 1:

Select month(date), count(id) as total_sold_products 
from Products 
where date between '2022-01-01' and '2022-12-31'

Query 2

Select month(date), count(id) as drinks_sold_products 
from Products where type = 'drinks' and date between '2022-01-01' and '2022-12-31'

I tried the union function but it summed count(id) twice and gave me only 2 columns

Many thanks!

>Solution :

Union is for attacking sets of data on top of each other. You need conditional aggregation or a join. See below.

    SELECT MONTH(date), 
        COUNT(*) AS total_sold_products,
        COUNT(CASE WHEN type = 'drinks' THEN 1 ELSE 0 END) AS drinks_sold_products
    FROM Products 
    WHERE date BETWEEN'2022-01-01' AND '2022-12-31'
    GROUP BY MONTH(date)