I have created a table which is keeps records of which product is sold by whom and how much each month; month total product cons_name 2020-01 10 xyz 123 2020-02 5 abc 456 2020-02 4 def 789 I was ...

How select all data using HAVING clause in WHERE condition?

I have created a table which is keeps records of which product is sold by whom and how much each month;

month	total	product	cons_name
2020-01	10	xyz	123
2020-02	5	abc	456
2020-02	4	def	789

I was creating a query from this table to find out who has sold over 500 products on certain products since the beginning of the year, but I was a bit confused at the time of writing. Because i am not needed to query how much it sells during the year, but how much it sells each month that’s i need to find. I can easily find more than 500 sales in total during a year with this query:

SELECT cons_name, product, SUM(total)
FROM TMP_REPORT
WHERE product IN ('abc','xyz')
GROUP BY cons_name, product
HAVING sum(total) > 500

But when it came to querying in detail I got this far:

SELECT 
  month,
  product,
  cons_name,
  total
FROM TMP_REPORT 
WHERE product IN ('abc','xyz')
AND cons_name IN 
              (SELECT 
                 cons_name
               FROM TMP_REPORT 
               WHERE 
                 product IN ('abc','xyz') 
               GROUP BY 
                 cons_name 
               HAVING sum(total) > 500)

The result of this query showed even the totals of sold product are not 500. For example, we would expect the cons_name named ‘123’ to not be in the query result for only 200 sold ‘abc’ products in a year, but it does exist because of where clause. I knew my mistakes but I don’t know that how to fix.

How can i do it?

Thanks for your help.

Answer

One approach uses SUM as an analytic function:

WITH cte AS (
    SELECT t.*, SUM(total) OVER (PARTITION BY cons_name, product) sum_total
    FROM TMP_REPORT t
    WHERE product IN ('abc', 'xyz')
)

SELECT *
FROM cte
WHERE sum_total > 500;

Advertisement

Answer