On 2012-05-21 11:17 AM, Don Wieland wrote:
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.
Yes: select a.id,count(*) from a join b using(...) join c using(...)
will multiply counts. The most effective solution is to move each
aggregation inside its own FROM clause subquery.
PB
-----
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:
dr_ther_qty = 25
dr_not_ther_qty = 22
dr_all_ther_qty = 47
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
