List:General Discussion« Previous MessageNext Message »
From:Don Wieland Date:May 21 2012 4:17pm
Subject:Query assistance...
View as plain text  
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:

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

Thread
Query assistance...Don Wieland21 May
  • Re: Query assistance...Peter Brawley21 May