CODEWITHSUNDEEP
sqloracle-databaseselectgroup-byhaving
Michu93
Michu93

Reputation: 5687

SQL query with GROUP BY and HAVING COUNT(condition) in ORACLE

I have three tables: temp, product and feed. I'll show on example:

select ri.id from temp ri
inner join product i on ri.id = to_char(i.val)
inner join feed f on f.product_id = i.product_id
where i.status = 'Finished'
and f.type = 'Type'
group by f.feed_id, ri.id
having COUNT(f.status = 'SUCCESS') < 1;

so I tried to get all ids from temp that have f.type = 'Type'. Problem is that for one feed.feed_id can be many rows because I could retrigger it 5 times and let's say 4 times it crashed but at 5th attempt it was SUCCESS, so for one feed.feed_id I would have 5 rows and only one would be with f.status = SUCCESS.

Error which I receive for this query is ORA-00907: missing right parenthesis which makes me totally confused.

feed table: feed_id, status, type I am interested in all feed_id which don't have even one status='SUCCESS' for type='TYPE'

Upvotes: 0

Views: 1235

Answers (1)

Nick
Nick

Reputation: 147166

You can't COUNT a boolean expression in Oracle, you can use a CASE expression instead e.g.

HAVING COUNT(CASE WHEN f.status = 'SUCCESS' THEN 1 END) < 1

This expression returns NULL when the condition is false, so it will only count the rows for which the condition is true (since COUNT of an expression ignores NULL values).

Note also (as @GordonLinoff points out in the comments) that since COUNT cannot return a negative number, it is cleaner (and would be more efficient) to simply compare the result for equality with 0, rather than being less than 1:

HAVING COUNT(CASE WHEN f.status = 'SUCCESS' THEN 1 END) = 0

Upvotes: 3

Related Questions