I have got this query that returns the correct rows I want to display, BUT the columns dr_ther_qty, dr_not_ther_qty, and dr_all_ther_qty are not calculating correctly:
--- START QUERY ONE ---
SELECT q1.* FROM
(SELECT apt.user_id, apt.client_id, c.last_name, c.first_name, MIN(apt.time_start) AS stime, FROM_UNIXTIME(MIN(apt.time_start),'%Y-%m- %d') AS ftime,
(SELECT count(*) FROM tl_appt WHERE client_id = apt.client_id AND appt_status_id = '3' AND time_start < apt.time_start) AS previous,
(SELECT count(*) FROM tl_appt WHERE client_id = apt.client_id AND user_id = apt.user_id AND appt_status_id = '3' AND FROM_UNIXTIME(apt.time_start,'%Y-%m-%d') between '2012-01-01' and '2012-05-20') AS dr_ther_qty,
(SELECT count(*) FROM tl_appt WHERE client_id = apt.client_id AND user_id != apt.user_id AND appt_status_id = '3' AND FROM_UNIXTIME(apt.time_start,'%Y-%m-%d') between '2012-01-01' and '2012-05-20') AS dr_not_ther_qty,
(SELECT DISTINCT count(*) FROM tl_appt WHERE client_id = apt.client_id AND appt_status_id = '3' AND FROM_UNIXTIME(apt.time_start,'%Y-%m-%d') between '2012-01-01' and '2012-05-20') AS dr_all_ther_qty
FROM tl_appt apt
LEFT JOIN tl_rooms r on r.room_id = apt.room_id LEFT JOIN tl_clients c on c.client_id = apt.client_id LEFT JOIN tl_users u on u.user_id = apt.user_id
WHERE apt.appt_id IS NOT NULL AND FROM_UNIXTIME(apt.time_start,'%Y-%m- %d') between '2012-01-01' and '2012-05-20' AND apt.appt_status_id = '3' and r.location_id = '2' and apt.user_id IN (14, 503) GROUP BY apt.user_id, apt.client_id ORDER BY u.last_name, u.first_name, c.last_name, c.first_name) as q1
WHERE q1.previous > 0;
--- END QUERY ONE ---
The totals of dr_ther_qty, dr_not_ther_qty, and dr_all_ther_qty are not the same if I broke them out into separate queries:
I think it is something to do with the GROUP BY - it is multiplying rows. Basically, the rows are correct and I want to use the user_id and client_id to calculate the SUB-SELECTS.
Can someone explain why when I run in the MAIN query I get this:
BUT when I break out that client into the separate queries, I get
SELECT count(*) as dr_ther_qty FROM tl_appt WHERE client_id = 161 AND user_id = 503 AND appt_status_id = '3' AND FROM_UNIXTIME(time_start,'%Y-%m-%d') between '2012-01-01' and '2012-05-20';
dr_ther_qty = 6
SELECT count(*) as dr_not_ther_qty FROM tl_appt WHERE client_id = 161 AND user_id != 503 AND appt_status_id = '3' AND FROM_UNIXTIME(time_start,'%Y-%m-%d') between '2012-01-01' and '2012-05-20';
dr_not_ther_qty = 2
SELECT count(*) as dr_all_ther_qty FROM tl_appt WHERE client_id = 161 AND appt_status_id = '3' AND FROM_UNIXTIME(time_start,'%Y-%m-%d') between '2012-01-01' and '2012-05-20';
dr_all_ther_qty = 8
I appreciate any enlightenment on this. Thanks!
Don
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
May 21
Peter Brawley Re: Query assistance...
May 21, 2012; 13:27
Peter Brawley
Re: Query assistance...
Search
Lasso Programming
This site manages and broadcasts several email lists pertaining to Lasso Programming and technologies related and used by Lasso developers. Sign up today!