CODEWITHSUNDEEP
mysqlsqljoincount
user
user

Reputation: 395

mysql count (SUM CASE) with join on same table

I have a query in which I need to compare 2 rows in the same table (not successive) in which the id of one must be less than the other, and the name of the fields must be equal to specific values. If I don’t include the join I can pull the data but when I try and join the data I get ‘column e1 is unknown’. I’m assuming its related to the count but I can’t seem to fix it. Any ideas?

SELECT 
case 
when ( extract( HOUR FROM e1.created_at) = 0 ) then '12am'
when ( extract( HOUR FROM e1.created_at) < 12) then concat(extract(HOUR FROM e1.created_at), 'am') 
when ( extract( HOUR FROM e1.created_at) = 12) then '12pm'
when ( extract( HOUR FROM e1.created_at) > 12) then concat( ( extract(HOUR FROM e1.created_at) -12 ), 'pm') 
end  AS "Time", 
sum(case when e1.result = “result1" and e2.result =“result2" and e1 < e2 then 1 else 0 end) AS ‘My Result'
from event e1 
join event e2 on e2.secondary_id = e1.secondary_id
where e1.started_at > '2018-02-19 00:00:00' and e1.started_at < '2018-02-20 00:00:00'
group by extract(hour from e1.created_at)

Upvotes: 1

Views: 348

Answers (1)

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726509

Since e1 and e2 are table aliases, this CASE expression is incorrect:

e1 < e2

You need to specify which fields from the event table you are comparing. Assuming that you are comparing timestamps, the sum expression should look like this:

sum(case when e1.result = “result1" and e2.result =“result2" and e1.started_at < e2.started_at then 1 else 0 end) AS ‘My Result'

Upvotes: 3

Related Questions