From: Peter Brawley
Date: May 21 2012 6:27pm
Subject: Re: Query assistance...
List-Archive: http://lists.mysql.com/mysql/227473
Message-Id: <4FBA8916.1090805@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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