need an additional column in SQL output

I have a table called product_info where there are two columns(product, product_id).There are 5 ‘product_id’ and 10 ‘product’. I wrote the following code to list products and their count.Now I need to create an additional column called ‘favorite_product’ if the ‘product’ count is more than 3.When I tried with a couple of WHERE clause options, it filtered out my existing column (product_count) which I need to keep intact in the output. How can I do that? Any help would be appreciated.

SELECT product AS Product_Name, 
       COUNT (product) AS Product_Count
FROM product_info
GROUP BY product

Answer

Just use a case expression:

SELECT product AS Product_Name, COUNT(*) AS Product_Count,
       (CASE WHEN COUNT(*) > 3 THEN 1 ELSE 0 END) as is_favorite_flag
FROM product_info
GROUP BY product;