retrieve sales data that is only sold from the certain date to another date-CodePudding

I'm just learning SQL, using Postgres. I have fruit sales data, how can I retrieve fruit that is only sold from the certain date to another date. I use a query like this but error

SELECT product_name,
       min (purchase_date) AS purchase_date
FROM sales
where purchase_date not in (purchase_date <= '2021-01-01' and purchase_date >= '2021-01-10')
GROUP BY product_name
ORDER by product_name asc ;

CodePudding user response：

It is BETWEEN you're looking for, I presume.

where purchase_date between '2021-01-01' and '2021-01-10'

You used negative logic (invalid, though), so I guess that you're looking for fruits sold between 1st and 10t of January 2021.

CodePudding user response：

You can't do this with a simple select, as that doesn't care about what other rows are doing. But you should be able to do it in a HAVING clause to filter out based on the aggregates:

SELECT product_name,
           min (purchase_date) AS purchase_date
    FROM sales
    GROUP BY product_name
    HAVING min (purchase_date)  >= '2021-01-01' and max(purchase_date) <= '2021-01-10'
    ORDER by product_name asc ;