sql查询计算一个查询中的多个已定义列

Question

I have been looking through this website to try and a) understand my problem better and b) to to find some help to solve it but it seems everything i've seen is slightly different to my problem so im here to ask for some advice please :)

i have the following query

SELECT *, 
(date_end < 1392569012) AS expired, 
(date_start > 1392569012) AS pending, 
(date_end > 1392569012 && date_start <= 1392569012) AS active 
FROM tbl_events AS event 
LEFT JOIN tbl_event_events AS ee ON event.event_id = ee.event_id
WHERE event.event_type = 5 && event.user_id = '".$user->getUserID()."'
ORDER BY expired,pending,active

which determins the status of a listing.

now what i want to do is count the number of active, pending and expired listings

this is my updated query, but it only returns 1 row but the correct sum values?

SELECT *, 
SUM(date_end < 1392569012) AS expiredCount,
SUM(date_start > 1392569012) AS pendingCount, 
SUM(date_end > 1392569012 && date_start <= 1392569012) AS activeCount,              
(date_end < 1392569012) AS expired, 
(date_start > 1392569012) AS pending, 
(date_end > 1392569012 && date_start <= 1392569012) AS active 
FROM tbl_events AS event 
LEFT JOIN tbl_job_events AS jobvent ON event.event_id = jobvent.event_id
LEFT JOIN tbl_job_department AS jdept ON event.event_id = jdept.event_id
LEFT JOIN tbl_departments as dept ON jdept.department_id = dept.department_id 
WHERE event.event_type = 2 && event.user_id = '".$user->getUserID()."'
ORDER BY expired,pending,active 

could someone help me get this working please!

thanks for any help

Luke

Answer 1

since active will be 1 for that filter and 0 for others, add sum to the active filter to get the count of rows which satisfies that filter.

SELECT *, ( SELECT  SUM(date_end > 1392569012 && date_start <= 1392569012)
FROM tbl_events AS event 
LEFT JOIN tbl_event_events AS ee ON event.event_id = ee.event_id
WHERE event.event_type = 5 && event.user_id = '".$user->getUserID()."'
ORDER BY expired,pending,active),
(date_end < 1392569012) AS expired, 
(date_start > 1392569012) AS pending, 
(date_end > 1392569012 && date_start <= 1392569012) AS active 
FROM tbl_events AS event 
LEFT JOIN tbl_event_events AS ee ON event.event_id = ee.event_id
WHERE event.event_type = 5 && event.user_id = '".$user->getUserID()."'
ORDER BY expired,pending,active