Re: Query of a query inconsistancies
Logically, they're different. You shouldn't get any results by moving the orderdate condition to the bottom query, because qryGetRepeatSales doesn't have a date column --- Todd [EMAIL PROTECTED] wrote: I have these 2 queries: cfquery datasource=#Application.Read# name=qryGetRepeatSales SELECT *, COUNT(*) AS totalcount, SUM(Cost) AS totalcost FROM orders, orderitems WHERE orders.OrderID = orderitems.OrderID AND OrderDate = '#DateFormat(StartTime, -mm-dd)#' GROUP BY UserID HAVING totalcount 1 /cfquery cfquery dbtype=query name=qrySubGetRepeatSales SELECT SUM(totalcost) AS totalcost, COUNT(totalcount) AS totalcount FROM qryGetRepeatSales /cfquery The problem I'm having is when the OrderDate line is in the top query, I get the correct results.If I move it to the bottom query, the totalcost and totalcount end up being 1 or 2 less.The date filter really needs to be in the bottom query.What am I missing? [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: Query of a query inconsistancies
It does.On the first line I'm doing a SELECT *.that would include the OrderDate column, would it not? - Original Message - From: I-Lin Kuo [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Thursday, July 08, 2004 10:31 AM Subject: Re: Query of a query inconsistancies Logically, they're different. You shouldn't get any results by moving the orderdate condition to the bottom query, because qryGetRepeatSales doesn't have a date column --- Todd [EMAIL PROTECTED] wrote: I have these 2 queries: cfquery datasource=#Application.Read# name=qryGetRepeatSales SELECT *, COUNT(*) AS totalcount, SUM(Cost) AS totalcost FROM orders, orderitems WHERE orders.OrderID = orderitems.OrderID AND OrderDate = '#DateFormat(StartTime, -mm-dd)#' GROUP BY UserID HAVING totalcount 1 /cfquery cfquery dbtype=query name=qrySubGetRepeatSales SELECT SUM(totalcost) AS totalcost, COUNT(totalcount) AS totalcount FROM qryGetRepeatSales /cfquery The problem I'm having is when the OrderDate line is in the top query, I get the correct results.If I move it to the bottom query, the totalcost and totalcount end up being 1 or 2 less.The date filter really needs to be in the bottom query.What am I missing? [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: Query of a query inconsistancies
Todd Ashworth wrote: Todd [EMAIL PROTECTED] wrote: cfquery datasource=#Application.Read# name=qryGetRepeatSales SELECT *, COUNT(*) AS totalcount, SUM(Cost) AS totalcost FROM orders, orderitems WHERE orders.OrderID = orderitems.OrderID AND OrderDate = '#DateFormat(StartTime, -mm-dd)#' GROUP BY UserID HAVING totalcount 1 /cfquery It does.On the first line I'm doing a SELECT *.that would include the OrderDate column, would it not? Let me guess, you are using MySQL. In standard SQL that would not include the OrderDate column because each field you select without aggregating it needs to be in the list of fields you group by. So in standard SQL this query can not produce any fields other then totalcount, totalcost (both aggregates) and UserID (group by). For some reason in MySQL this braindead syntax is allowed, but even the manual says the result may be 'unpredictable', which appears to be exactly what you are encountering: http://dev.mysql.com/doc/mysql/en/GROUP-BY-hidden-fields.html It is better to stay away from non standard SQL unless you understand every detail of the consequences. Jochem [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: Query of a query inconsistancies
Ah. That link explains it all.The OrderDate fields are not unique. Thanks. - Original Message - From: Jochem van Dieten [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Thursday, July 08, 2004 17:38 PM Subject: Re: Query of a query inconsistancies Let me guess, you are using MySQL. In standard SQL that would not include the OrderDate column because each field you select without aggregating it needs to be in the list of fields you group by. So in standard SQL this query can not produce any fields other then totalcount, totalcost (both aggregates) and UserID (group by). For some reason in MySQL this braindead syntax is allowed, but even the manual says the result may be 'unpredictable', which appears to be exactly what you are encountering: http://dev.mysql.com/doc/mysql/en/GROUP-BY-hidden-fields.html It is better to stay away from non standard SQL unless you understand every detail of the consequences. Jochem [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: Query of a query inconsistancies
For some reason in MySQL this braindead syntax is allowed, but even the manual says the result may be 'unpredictable' It's not totally brain dead.I agree that if you don't know what you're doing, you shouldn't use it, but it is nice where you've got a series of columns that you're grouping on, but one of them is unique.The only way it's unpredictable is if you're using it wrong.But then that's the case with anything. Cheers, barneyb [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: Query of a query inconsistancies
Barney Boisvert wrote: For some reason in MySQL this braindead syntax is allowed, but even the manual says the result may be 'unpredictable' It's not totally brain dead. I disagree :) I agree that if you don't know what you're doing, you shouldn't use it, but it is nice where you've got a series of columns that you're grouping on, but one of them is unique. If only one of them is unique, you need to normalize your data :) But even in the case you are working with such a schema, go for listing all the columns in the group by, especially if there is no way of enforcing that all future data will also have those characteristics. It is just to prone to future errors, not just the usual errors introduced by changing code, but also errors introduced by changing data. Jochem [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: Query of a query inconsistancies
If only one of them is unique, you need to normalize your data :) I need to pull out a list of users and the number of hours they've worked in the past year: SELECT user.userID, user.firstname, user.lastname, SUM(timelog.endTime - timelog.startTime) AS timeWorked FROM user INNER JOIN timelog ON user.userID = timelog.timelogID GROUP BY user.userID [ , user.firstname, user.lastname ] ORDER BY user.lastname, user.firstname, user.userID Why bother having the full select list (minus the aggregate column) in the GROUP BY clause, when simply 'user.userID' will do the job?There's nothing denormalized about the schema in question: USER: userID Firstname Lastname Username Password Email ... TIMELOG: timelogID userID startTime endTime logEntry -Original Message- From: Jochem van Dieten [mailto:[EMAIL PROTECTED] Sent: Thursday, July 08, 2004 3:06 PM To: CF-Talk Subject: Re: Query of a query inconsistancies Barney Boisvert wrote: For some reason in MySQL this braindead syntax is allowed, but even the manual says the result may be 'unpredictable' It's not totally brain dead. I disagree :) I agree that if you don't know what you're doing, you shouldn't use it, but it is nice where you've got a series of columns that you're grouping on, but one of them is unique. If only one of them is unique, you need to normalize your data :) But even in the case you are working with such a schema, go for listing all the columns in the group by, especially if there is no way of enforcing that all future data will also have those characteristics. It is just to prone to future errors, not just the usual errors introduced by changing code, but also errors introduced by changing data. Jochem [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: Query of a query inconsistancies
Barney Boisvert wrote: If only one of them is unique, you need to normalize your data :) I need to pull out a list of users and the number of hours they've worked in the past year: SELECT user.userID, user.firstname, user.lastname, SUM(timelog.endTime - timelog.startTime) AS timeWorked FROM user INNER JOIN timelog ON user.userID = timelog.timelogID GROUP BY user.userID [ , user.firstname, user.lastname ] ORDER BY user.lastname, user.firstname, user.userID Why bother having the full select list (minus the aggregate column) in the GROUP BY clause, when simply 'user.userID' will do the job? As long as your data is normalized, standard SQL doesn't require you to have anything more in the GROUP BY clause as MySQL does as long as you are smart about your SQL: SELECT u.userID, u.firstname, u.lastname, t.timeWorked FROM user u INNER JOIN ( SELECTtimelogID, SUM(endTime - startTime) AS timeWorked FROMtimelog GROUP BY timelogID ) t ON u.userID = t.timelogID ORDER BY user.lastname, user.firstname, user.userID Naturally this requires you to have a DBMS that supports subqueries and this is a query one would typically implement using a view if it occured frequently. I believe the (then) lack of support for subqueries and the current lack of support for views in MySQL is a better explanation for the presence of this 'feature' as this being an omission in the SQL standard. Jochem [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: Query of a query inconsistancies
That's still not the same query; it'll only work if each user only has a single record in the timelog table.Or did you mistype and all the 'timelogID's in the subquery should be replaced with 'userID's? You make a valid point.Not sure the jump to that's why MySQL has that feature is really reasonable, but it's damn near irrelevant anyway. Cheers, barneyb -Original Message- From: Jochem van Dieten [mailto:[EMAIL PROTECTED] Sent: Thursday, July 08, 2004 3:59 PM To: CF-Talk Subject: Re: Query of a query inconsistancies Barney Boisvert wrote: If only one of them is unique, you need to normalize your data :) I need to pull out a list of users and the number of hours they've worked in the past year: SELECT user.userID, user.firstname, user.lastname, SUM(timelog.endTime - timelog.startTime) AS timeWorked FROM user INNER JOIN timelog ON user.userID = timelog.timelogID GROUP BY user.userID [ , user.firstname, user.lastname ] ORDER BY user.lastname, user.firstname, user.userID Why bother having the full select list (minus the aggregate column) in the GROUP BY clause, when simply 'user.userID' will do the job? As long as your data is normalized, standard SQL doesn't require you to have anything more in the GROUP BY clause as MySQL does as long as you are smart about your SQL: SELECT u.userID, u.firstname, u.lastname, t.timeWorked FROM user u INNER JOIN ( SELECTtimelogID, SUM(endTime - startTime) AS timeWorked FROMtimelog GROUP BY timelogID ) t ON u.userID = t.timelogID ORDER BY user.lastname, user.firstname, user.userID Naturally this requires you to have a DBMS that supports subqueries and this is a query one would typically implement using a view if it occured frequently. I believe the (then) lack of support for subqueries and the current lack of support for views in MySQL is a better explanation for the presence of this 'feature' as this being an omission in the SQL standard. Jochem [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: Query of a query inconsistancies
Barney Boisvert wrote: That's still not the same query; it'll only work if each user only has a single record in the timelog table.Or did you mistype and all the 'timelogID's in the subquery should be replaced with 'userID's? Only if you mistyped it in the original :-) Jochem [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Query of a query inconsistancies
I have these 2 queries: cfquery datasource=#Application.Read# name=qryGetRepeatSales SELECT *, COUNT(*) AS totalcount, SUM(Cost) AS totalcost FROM orders, orderitems WHERE orders.OrderID = orderitems.OrderID AND OrderDate = '#DateFormat(StartTime, -mm-dd)#' GROUP BY UserID HAVING totalcount 1 /cfquery cfquery dbtype=query name=qrySubGetRepeatSales SELECT SUM(totalcost) AS totalcost, COUNT(totalcount) AS totalcount FROM qryGetRepeatSales /cfquery The problem I'm having is when the OrderDate line is in the top query, I get the correct results.If I move it to the bottom query, the totalcost and totalcount end up being 1 or 2 less.The date filter really needs to be in the bottom query.What am I missing? [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
