Geek asks and answers

SQL select if count

I have two tables Incident (id, date) Reminder (incident identifier, type) internal connection to event.id = reminder.incidentID

and I want to accept case.id only if it has more than 4 reminder.type = 15 what I think

SELECT incident.id
FROM incident
INNER JOIN reminder ON incident.id = reminder.incidentid 
HAVING COUNT (reminder.remindertype = "something") >5
GROUP BY incident.incidentcode

In erroe I get

Line 6: Invalid syntax next to '='.

What can I do?

+5
source share
3 answers

COUNT does not work.

Try:

select incident.id
from incident
inner join reminder
on incident.id = reminder.incidentid
WHERE reminder.remindertype = "something"
group by incident.incidentcode 
having count (reminder.remindertype) >5
+2
source

offer group bymust be announced before the offerhaving

select incident.id
from incident
inner join reminder
on incident.id = reminder.incidentid
group by incident.incidentcode 
having count (reminder.remindertype) >5
+2
source

You are close:

select incident.id
from incident inner join
     reminder
     on incident.id = reminder.incidentid
group by incident.incidentcode 
having sum(case when reminder.remindertype = "something" then 1 else 0 end) >5

The original syntax will not work in most dialects of SQL. You need conditional aggregation.

+1
source

More articles:


All Articles