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
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