Query assistance...
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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Query assistance...
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
Re: [PHP] mySQL query assistance...
On Mon, Nov 29, 2010 at 14:35, Don Wieland d...@dwdataconcepts.com wrote: Hi all, Is there a list/form to get some help on compiling mySQL queries? I am executing them via PHP, but do not want to ask for help here if it is no the appropriate forum. Thanks ;-) Yes. For MySQL queries, write to the MySQL General list at my...@lists.mysql.com. For PHP-specific database questions (for any database backend, not strictly MySQL), such as problems in connecting to the database, questions on support for database platform/version, or even query processing, you should use php...@lists.php.net. For your convenience, both have been CC'd on this email. -- /Daniel P. Brown Dedicated Servers, Cloud and Cloud Hybrid Solutions, VPS, Hosting (866-) 725-4321 http://www.parasane.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: [PHP] mySQL query assistance...
This is the general list. If your problem is with MySQL and queries, let us know. Best regards. -- WB 2010/11/29 Daniel P. Brown daniel.br...@parasane.net On Mon, Nov 29, 2010 at 14:35, Don Wieland d...@dwdataconcepts.com wrote: Hi all, Is there a list/form to get some help on compiling mySQL queries? I am executing them via PHP, but do not want to ask for help here if it is no the appropriate forum. Thanks ;-) Yes. For MySQL queries, write to the MySQL General list at my...@lists.mysql.com. For PHP-specific database questions (for any database backend, not strictly MySQL), such as problems in connecting to the database, questions on support for database platform/version, or even query processing, you should use php...@lists.php.net. For your convenience, both have been CC'd on this email. -- /Daniel P. Brown Dedicated Servers, Cloud and Cloud Hybrid Solutions, VPS, Hosting (866-) 725-4321 http://www.parasane.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=wagnerbianch...@gmail.com
SELECT Query assistance please
I have two tables EVENT and PROJECTCODE EVENT.ID EVENT.ID_PROJECTCODE PROJECTCODE.ID PROJECTCODE.Name EVENT PROJECTCODE ID = 1 ID_PROJECTCODE = 0 ID = 1 ID = 2 ID_PROJECTCODE = 0 ID = 2 ID = 3 ID_PROJECTCODE = 1 ID = 3 ID = 4 ID_PROJECTCODE = 4 ID = 4 SELECT PROJECTCODE.ID AS 'ID', PROJECTCODE.Name AS 'Reference' FROM PROJECTCODE Not quite sure on the join or where claus here, I tried 3 or 4 different ways and can't seem to get what I want. What I want out of the results is PROJECTCODE.ID = 2 and 3, that being all records in PROJECTCODE that do not appear in EVENT Luc -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SELECT Query assistance please
Got it, thanks SELECT PROJECTCODE.ID AS 'ID', PROJECTCODE.Name AS 'Reference', EVENT.ID, EVENT.ID_PROJECTCODE FROM PROJECTCODE LEFT JOIN EVENT ON PROJECTCODE.ID = EVENT.ID_PROJECTCODE WHERE EVENT.ID IS NULL ORDER BY PROJECTCODE.Name -Original Message- From: Luc Foisy Sent: Thursday, November 13, 2003 2:01 PM To: MYSQL-List (E-mail) Subject: SELECT Query assistance please I have two tables EVENT and PROJECTCODE EVENT.ID EVENT.ID_PROJECTCODE PROJECTCODE.ID PROJECTCODE.Name EVENT PROJECTCODE ID = 1 ID_PROJECTCODE = 0 ID = 1 ID = 2 ID_PROJECTCODE = 0 ID = 2 ID = 3 ID_PROJECTCODE = 1 ID = 3 ID = 4 ID_PROJECTCODE = 4 ID = 4 SELECT PROJECTCODE.ID AS 'ID', PROJECTCODE.Name AS 'Reference' FROM PROJECTCODE Not quite sure on the join or where claus here, I tried 3 or 4 different ways and can't seem to get what I want. What I want out of the results is PROJECTCODE.ID = 2 and 3, that being all records in PROJECTCODE that do not appear in EVENT Luc -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
