MySQL conditional sum query?

jdiver

I'm not sure if what I want to do is possible using a single MySQL query, but I figured it wouldn't hurt to ask. I have three tables involved, listed below with the relevant fields:

users
- user_id
- location_id

orders
- order_id
- user_id
- event_id

events
- event_id
- event_date (unix timestamp)

It's for a system where a user places an order to attend an event at a set date and time. What I need is to get a count of the total # of users, grouped by location, who have attended an event 2 times in the last 90 days.

I'd like to do this in one query if possible. I'm wondering if it's possible to use a combination of COUNT and SUM (IF ...) in the query to count users who have attended twice in the last 90 days.

Any help or ideas would be greatly appreciated. Thanks in advance.

Roger_Ramjet

No you cannot do that cos you've got a CHASM TRAP (google it)

Sxooter

Actually, given his requirements, I'm not sure it is a chasm trap.

select * from users u join orders o on (u.user_id=o.order_id) join events e on (o.event_id=e.event_id)

gives us all the users who have links to given events.

add a where clause, we get the last 90 days:

where event_date > now() - interval '90 days'

group it by users:

group by u.user_id

select those who have attended 2 events:

having count(e.event_id) = 2 (or >=2 if you want 2 or MORE events)

Not sure there's a chasm there.

Roger_Ramjet

Well when you take it like that there is no chasm, Sxooter, but you forgot the group by location.

Assuming location will have multiple users who have multiple events, many of which will overlap, it looks like a classic chasm to me.

Location 1:M Users M:M Events

Sxooter

Oh yeah, that location thing. Hmmm. I tend to agree now. Hadn't noticed that part.

Sxooter

I think you need a location table separated out here. Just thinking off the top of my head. Not gonna guarantee that one.

Roger_Ramjet

Well once you know its there you can work with it. I think in this case I would use your query as a subquery to get the user ids that you need in an outer query to group and count by location.


$sql = "SELECT COUNT(u.user_id) AS total, l.location FROM locations l INNER JOIN  

(select user_id, location_id from users u join orders o on (u.user_id=o.order_id) join events e on (o.event_id=e.event_id) where event_date > now() - interval '90 days' group by u.user_id having count(e.event_id) >= 2) AS u USING(location_id) GROUP BY l.location";

Something like that anyway.

tomhath

I still don't see the chasm. As I read it, the location is an attribute of the user, not the event. So it's no different than if he wanted it broken out by gender.