sql query to count multiple defined columns within one query

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

Volkan Ulukut · Accepted Answer · 2014-02-19 23:06:06Z

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

edited Feb 19, 2014 at 23:06

answered Feb 19, 2014 at 22:24

Volkan Ulukut

4,2281 gold badge24 silver badges40 bronze badges

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

10 Comments

user2886669 Over a year ago

on further inspection this is only returning a max of 1 row for each type of listing? any ideas? thanks

Volkan Ulukut Over a year ago

you mean this query returns 1 row? or the SUM field returns value 1?

user2886669 Over a year ago

the query returns 1 row.

Volkan Ulukut Over a year ago

added sum query as a subquery try now

user2886669 Over a year ago

it didnt like the first set order by's so i removed them and now its not returning any rows? thanks

|

Collectives™ on Stack Overflow

sql query to count multiple defined columns within one query

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related