mysql count (SUM CASE) with join on same table

Question

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)

Edit your question and provide sample data and desired results. However, it is probably the condition e1 < e2. — Gordon Linoff
– Gordon Linoff, Commented Feb 27, 2018 at 12:56

Sergey Kalinichenko · Accepted Answer · 2018-02-27 12:58:51Z

3

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'

answered Feb 27, 2018 at 12:58

Sergey Kalinichenko

729k85 gold badges1.2k silver badges1.6k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

mysql count (SUM CASE) with join on same table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related