'HOW TO GROUP ROWS OF A TABLE WITH COLUMN DATE

I want a query to display the number of containers that only contain products so the date is less than 01/01/2019 and date is not null..

Example:

my table : tab

Num_Container Num_Product Date_Product
1 A1 01/01/2020
1 A2 01/01/2018
1 A3 01/01/2021
2 A4 01/01/2017
2 A5 01/01/2018
2 A6 01/01/2019
3 A7 Null
3 A8 01/01/2019
3 A9 01/01/2016

The expected result is:

Num_Container
2

The containers must contain only products less than or equal to the date 01/01/2019 and date is not null.

sqloracle


Solution 1:[1]

You can try to use condition aggregate function in HAVING which can use reverse logic get counting is 0

SELECT Num_Container
FROM tab
GROUP BY Num_Container
HAVING COUNT(CASE WHEN Date_Product > TO_DATE('01/01/2019', 'DD/MM/YYYY') OR Date_Product IS NULL THEN 1 END) = 0

Solution 2:[2]

maybe like this

select Num_Container
from yourtable 
group by Num_Container
having max(Date_Product)< DATE'2019-01-01'

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2

Related Questions