CODEWITHSUNDEEP
mysql
cube
cube

Reputation: 13

Joining two select queries but one is grouped

I have a (entrance)ticket table with a ticket_code, a timestamp_processed and an event code.

i.e.:

ticket_code         event             timestamp_processed
---------------------------------------------------------
44185909829105549   TICKET_ACCEPTED   1434636748

Now I want to count the occurrence of every event = 'TICKET_ACCEPTED' until the end of a specified time frame.

I use this query:

SELECT ticket_code, COUNT(ticket_code) count 
FROM rest_history 
WHERE timestamp_processed <= :endTime AND event = 'TICKET_ACCEPTED' 
GROUP BY ticket_code

Now I want to get only the count and ticket_codes of a specific time range with a startTime.

Here is the query to get all ticket_codes in a specific time range:

SELECT ticket_code 
FROM rest_history 
WHERE timestamp_processed >= :startTime AND 
timestamp_processed <= :endTime AND 
event = 'TICKET_ACCEPTED'

Now I want to INNER JOIN both queries or build one using a subselect, but I can't get the right solution. Can someone give me a hint?

Update:

In the end I want a GROUP BY ticket_code with the individual count of each occurrence of every ticket_code with the event = 'TICKET_ACCEPTED' in the specific timeframe but the count value should include all occurrences until endFrame and not just since "startTime".

i.e:

[ticket_code] => 1434636748 [count] => 3
[ticket_code] => 3434636745 [count] => 1
[ticket_code] => 2434636546 [count] => 10

Upvotes: 1

Views: 32

Answers (2)

user4843530
user4843530

Reputation:

I think this would work:

select t1.ticket_code, t1.count from
(SELECT ticket_code, max(timestamp_processed) lasttime, COUNT(ticket_code) count 
FROM rest_history 
WHERE timestamp_processed <= :endTime AND event = 'TICKET_ACCEPTED' 
GROUP BY ticket_code) as t1
where t1.lasttime >= :startTime

by using max(timestamp), we are making sure that any ticket with a timestamp_processed >= starTime gets included with that greatest timestamp in the subquery, so that it then passes the test of the outer query.

Upvotes: 0

Kickstart
Kickstart

Reputation: 21533

Possibly joining the table against itself. Untested but something like this:-

SELECT a.ticket_code, COUNT(b.ticket_code) 
FROM rest_history a
LEFT OUTER JOIN rest_history b
ON a.ticket_code = b.ticket_code
AND b.timestamp_processed <= :endTime 
AND b.event = 'TICKET_ACCEPTED'
WHERE a.event = 'TICKET_ACCEPTED'
AND a.timestamp_processed >= :startTime 
AND a.timestamp_processed <= :endTime 
GROUP BY a.ticket_code

Upvotes: 1

Related Questions