CODEWITHSUNDEEP
sql
D. Müller
D. Müller

Reputation: 3426

SQL: Conditions in grouped data

I have the following table:

(row)  | date        | transaction  | articleno  | salesamount
==============================================================
(1)    | 2017-01-01  | 1            | A          | 1.0
(2)    | 2017-01-01  | 1            | B          | 2.0
(3)    | 2017-01-01  | 1            | C          | 2.0
(4)    | 2017-01-01  | 1            | null       | 0.0
--------------------------------------------------------------
(5)    | 2017-01-02  | 2            | X          | 1.0
(6)    | 2017-01-02  | 2            | Y          | 2.0
...

I want to make a SQL statement which goes through my table and looks for baskets/groups (identified by date and transaction combination), that have both

within the same basket (not only in one row). I need to give back the date/transaction combinations that fulfill the conditions above. In the example table above, the first group would be relevant and 2017-01-01 & 1 should be returned.

I tried to use the having condition after a group by date, transaction but this seems not to be able (having can only be used for columns or aggregations that refer to the columns of the group by clause).

How can I do this? Do I need an inner SQL?

Upvotes: 1

Views: 90

Answers (3)

gmed
gmed

Reputation: 140

SELECT date, transaction FROM yourtable WHERE salesamount > 1.0 AND articleno is NULL

Upvotes: 0

GrabNewTech
GrabNewTech

Reputation: 641

Please try the below query(Assumed Oracle Database).

SELECT TO_CHAR(date,'YYYY/MM/DD'), transaction
FROM TABLE_NAME
WHERE article is null
AND salesamount > 1.0
GROUP BY TO_CHAR(date,'YYYY/MM/DD'), transaction

Upvotes: 0

Oto Shavadze
Oto Shavadze

Reputation: 42753

try this:

SELECT t1.* FROM (
    SELECT date,transaction
    FROM yourtable
    WHERE salesamount > 1.0  
    GROUP BY date,transaction
) t1
INNER JOIN (
    SELECT date,transaction
    FROM yourtable
    WHERE articleno IS NULL
    GROUP BY date,transaction
) t2
ON t1.date = t2.date AND t1.transaction = t2.transaction

Upvotes: 2

Related Questions