filtering using having by clause result in unknown column in having clause

Question

i have a table with these columns: order_date (in yyyy-mm-dd format), order_status, product_sub_category, sales. the data consists of transactions from 2009 onwards. what i want to do is to find the total sales of each product categories in between the year 2011 and 2012. to do that, i use these query:

SELECT 
   YEAR(order_date) AS years, 
   product_sub_category,
   SUM(sales) AS sales 
   FROM dqlab_sales_store
WHERE
   order_status = 'order finished' 
   AND product_sub_category = 'Chairs & Chairmats' 
   OR product_sub_category = 'Office Machines' 
   OR product_sub_category = 'Tables' 
   OR product_sub_category = 'Copiers and Fax' 
   OR product_sub_category = 'Telephones and Communication'
GROUP BY
   YEAR(order_date), product_sub_category
HAVING
   YEAR(order_date) > 2011;

the problem is that sql tells me unknown column 'order_date' in having clause

any solution? thank you

Answer 1

You can't use having and where in the same query. Try This:

    SELECT a.years, a.product_sub_category, sum(sales) as sales
 FROM(
        SELECT 
           YEAR(order_date) AS years, 
           product_sub_category,
           sales
        FROM dqlab_sales_store
        WHERE
           order_status = 'order finished' 
           AND (product_sub_category = 'Chairs & Chairmats' 
           OR product_sub_category = 'Office Machines' 
           OR product_sub_category = 'Tables' 
           OR product_sub_category = 'Copiers and Fax' 
           OR product_sub_category = 'Telephones and Communication')) as a
    GROUP BY
       a.years, 
       a.product_sub_category
    HAVING
       a.years > 2011;