RE: distinct count
You could try removing the a_items.END_TIME from your select and group by clauses to see if that does what you need, as if each of the values for a_items.END_TIME are different the query will create a row in the resultset for each value. Does that help? Alex -Original Message- From: Owens, Howard [mailto:[EMAIL PROTECTED] Sent: 12 March 2003 04:35 To: CF-Talk Subject: RE: distinct count Nope. That wasn't it. I removed the time stuff and it didn't change the results. Yet I can't see any real other difference between the queries. H. -Original Message- From: Owens, Howard [SMTP:[EMAIL PROTECTED] Sent: Tuesday, March 11, 2003 8:09 PM To: CF-Talk Subject:RE: distinct count OK, I just realized something about this query ... I have a similar query for another part of the site that is exactly the same query, except their is no date comparison. That's why I'm not getting the results I expect (the other query works the way I expect), because since each entry has a diffferent SMALLDATETIME value, it counts as one ... now I've got to figure out a work around ... any suggestions? H. -Original Message- From: Owens, Howard [SMTP:[EMAIL PROTECTED] Sent: Tuesday, March 11, 2003 2:01 PM To: CF-Talk Subject: SQL: distinct count The following query doesn't quite do what I want it to do, and I'm stumped on how to get what I want ... SELECT COUNT(a_items.WINE_TYPE_ID) AS WCOUNT, w_types.TYPE_ID, w_types.TYPE, a_items.TYPE_ID, a_items.END_TIME FROM w_types, a_items WHERE w_types.TYPE_ID = a_items.TYPE_ID AND DATEDIFF( minute, #createODBCDATETIME(dateAdd(h, -3, NOW()))#, a_items.END_TIME) = 0 GROUP BY w_types.TYPE_ID, w_types.TYPE, a_items.TYPE_ID, a_items.END_TIME ORDER BY TYPE What I need is something that returns: a_typeA (5) a_typeB (6) Where (n) is the number of that named type. What I'm getting is a_typeA (1) a_typeA (1) a_typeA (1) a_typeA (1) a_typeA (1) a_typeA (1) Anybody have any pointers on how to get what I need? (speaking strictly of the query, of course). H. ~~ Howard Owens Internet Operations Coordinator InsideVC.com/Ventura County Star [EMAIL PROTECTED] AIM: GoCatGo1956 ~~ ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: distinct count
The SQL padwan bows to greater wisdom. Avoiding combinig aggregates and 'real' data is a rule of thumb I picked up somewhere. It may have just been to keep me from botching things when I really didn't know /anything/ about SQL. :-) -- Ben Doom Programmer General Lackey Moonbow Software, Inc : -Original Message- : From: Dave Watts [mailto:[EMAIL PROTECTED] : Sent: Tuesday, March 11, 2003 10:09 PM : To: CF-Talk : Subject: RE: distinct count : : : Ben wrote: : : Remember -- it's generally a bad idea to run a query : selecting real data and aggregates (like count) at the : same time. : : Then how do I get both the aggregate info and the other : query columns I need for the same output? : : I disagree with the statement that it's a bad idea to run a query : selecting : real data and aggregates at the same time - if you want to ask a question : like how many sales per region, that's how you do it. : : Dave Watts, CTO, Fig Leaf Software : http://www.figleaf.com/ : voice: (202) 797-5496 : fax: (202) 797-5444 : : ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: distinct count
real data and aggregates at the same time is fine - so long as you realise the the real data is grouped too. i.e. if you are using agregate functions and real data in your select statement all of the columns referenced need to be grouped - whgich makes entire sense. I have met so many developers who ask me why doesn't this work: select firstname, surname, company, count(*) as 'num of employees' from tblemployees group by company -Original Message- From: Ben Doom [mailto:[EMAIL PROTECTED] Sent: 12 March 2003 14:39 To: CF-Talk Subject: RE: distinct count The SQL padwan bows to greater wisdom. Avoiding combinig aggregates and 'real' data is a rule of thumb I picked up somewhere. It may have just been to keep me from botching things when I really didn't know /anything/ about SQL. :-) -- Ben Doom Programmer General Lackey Moonbow Software, Inc : -Original Message- : From: Dave Watts [mailto:[EMAIL PROTECTED] : Sent: Tuesday, March 11, 2003 10:09 PM : To: CF-Talk : Subject: RE: distinct count : : : Ben wrote: : : Remember -- it's generally a bad idea to run a query : selecting real data and aggregates (like count) at the : same time. : : Then how do I get both the aggregate info and the other : query columns I need for the same output? : : I disagree with the statement that it's a bad idea to run a query : selecting : real data and aggregates at the same time - if you want to ask a question : like how many sales per region, that's how you do it. : : Dave Watts, CTO, Fig Leaf Software : http://www.figleaf.com/ : voice: (202) 797-5496 : fax: (202) 797-5444 : : ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: distinct count
Ah, this answer sounded so promising. Alas, it didn't do the trick. H. -Original Message- From: A.Little [SMTP:[EMAIL PROTECTED] Sent: Wednesday, March 12, 2003 1:28 AM To: CF-Talk Subject: RE: distinct count You could try removing the a_items.END_TIME from your select and group by clauses to see if that does what you need, as if each of the values for a_items.END_TIME are different the query will create a row in the resultset for each value. Does that help? Alex -Original Message- From: Owens, Howard [mailto:[EMAIL PROTECTED] Sent: 12 March 2003 04:35 To: CF-Talk Subject: RE: distinct count Nope. That wasn't it. I removed the time stuff and it didn't change the results. Yet I can't see any real other difference between the queries. H. -Original Message- From: Owens, Howard [SMTP:[EMAIL PROTECTED] Sent: Tuesday, March 11, 2003 8:09 PM To: CF-Talk Subject: RE: distinct count OK, I just realized something about this query ... I have a similar query for another part of the site that is exactly the same query, except their is no date comparison. That's why I'm not getting the results I expect (the other query works the way I expect), because since each entry has a diffferent SMALLDATETIME value, it counts as one ... now I've got to figure out a work around ... any suggestions? H. -Original Message- From: Owens, Howard [SMTP:[EMAIL PROTECTED] Sent: Tuesday, March 11, 2003 2:01 PM To: CF-Talk Subject:SQL: distinct count The following query doesn't quite do what I want it to do, and I'm stumped on how to get what I want ... SELECT COUNT(a_items.WINE_TYPE_ID) AS WCOUNT, w_types.TYPE_ID, w_types.TYPE, a_items.TYPE_ID, a_items.END_TIME FROM w_types, a_items WHERE w_types.TYPE_ID = a_items.TYPE_ID AND DATEDIFF( minute, #createODBCDATETIME(dateAdd(h, -3, NOW()))#, a_items.END_TIME) = 0 GROUP BY w_types.TYPE_ID, w_types.TYPE, a_items.TYPE_ID, a_items.END_TIME ORDER BY TYPE What I need is something that returns: a_typeA (5) a_typeB (6) Where (n) is the number of that named type. What I'm getting is a_typeA (1) a_typeA (1) a_typeA (1) a_typeA (1) a_typeA (1) a_typeA (1) Anybody have any pointers on how to get what I need? (speaking strictly of the query, of course). H. ~~ Howard Owens Internet Operations Coordinator InsideVC.com/Ventura County Star [EMAIL PROTECTED] AIM: GoCatGo1956 ~~ ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: distinct count
I take that back ... DOH! I was loading the revised query to the wrong directory. This change did work Thanks, Mr. Alex Little. H. -Original Message- From: Owens, Howard [SMTP:[EMAIL PROTECTED] Sent: Wednesday, March 12, 2003 10:51 AM To: CF-Talk Subject: RE: distinct count Ah, this answer sounded so promising. Alas, it didn't do the trick. H. -Original Message- From: A.Little [SMTP:[EMAIL PROTECTED] Sent: Wednesday, March 12, 2003 1:28 AM To: CF-Talk Subject:RE: distinct count You could try removing the a_items.END_TIME from your select and group by clauses to see if that does what you need, as if each of the values for a_items.END_TIME are different the query will create a row in the resultset for each value. Does that help? Alex -Original Message- From: Owens, Howard [mailto:[EMAIL PROTECTED] Sent: 12 March 2003 04:35 To: CF-Talk Subject: RE: distinct count Nope. That wasn't it. I removed the time stuff and it didn't change the results. Yet I can't see any real other difference between the queries. H. -Original Message- From: Owens, Howard [SMTP:[EMAIL PROTECTED] Sent: Tuesday, March 11, 2003 8:09 PM To: CF-Talk Subject:RE: distinct count OK, I just realized something about this query ... I have a similar query for another part of the site that is exactly the same query, except their is no date comparison. That's why I'm not getting the results I expect (the other query works the way I expect), because since each entry has a diffferent SMALLDATETIME value, it counts as one ... now I've got to figure out a work around ... any suggestions? H. -Original Message- From: Owens, Howard [SMTP:[EMAIL PROTECTED] Sent: Tuesday, March 11, 2003 2:01 PM To: CF-Talk Subject: SQL: distinct count The following query doesn't quite do what I want it to do, and I'm stumped on how to get what I want ... SELECT COUNT(a_items.WINE_TYPE_ID) AS WCOUNT, w_types.TYPE_ID, w_types.TYPE, a_items.TYPE_ID, a_items.END_TIME FROM w_types, a_items WHERE w_types.TYPE_ID = a_items.TYPE_ID AND DATEDIFF( minute, #createODBCDATETIME(dateAdd(h, -3, NOW()))#, a_items.END_TIME) = 0 GROUP BY w_types.TYPE_ID, w_types.TYPE, a_items.TYPE_ID, a_items.END_TIME ORDER BY TYPE What I need is something that returns: a_typeA (5) a_typeB (6) Where (n) is the number of that named type. What I'm getting is a_typeA (1) a_typeA (1) a_typeA (1) a_typeA (1) a_typeA (1) a_typeA (1) Anybody have any pointers on how to get what I need? (speaking strictly of the query, of course). H. ~~ Howard Owens Internet Operations Coordinator InsideVC.com/Ventura County Star [EMAIL PROTECTED] AIM: GoCatGo1956 ~~ ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: distinct count
I think you've overcomplicated the query. IIRC, it should look something like: select count(a_items.wine_type_id) as wcount where [whatever] group by a_items.wine_type_id order by [whatever] Remember -- it's generally a bad idea to run a query selecting real data and aggregates (like count) at the same time. -- Ben Doom Programmer General Lackey Moonbow Software, Inc : -Original Message- : From: Owens, Howard [mailto:[EMAIL PROTECTED] : Sent: Tuesday, March 11, 2003 5:01 PM : To: CF-Talk : Subject: SQL: distinct count : : : The following query doesn't quite do what I want it to do, and I'm stumped : on how to get what I want ... : : SELECT COUNT(a_items.WINE_TYPE_ID) AS WCOUNT, : w_types.TYPE_ID, : w_types.TYPE, : a_items.TYPE_ID, : a_items.END_TIME : FROM w_types, a_items : WHERE w_types.TYPE_ID = a_items.TYPE_ID : AND DATEDIFF( minute, #createODBCDATETIME(dateAdd(h, -3, NOW()))#, : a_items.END_TIME) = 0 : GROUP BY w_types.TYPE_ID, : w_types.TYPE, : a_items.TYPE_ID, : a_items.END_TIME : ORDER BY TYPE : : What I need is something that returns: : : a_typeA (5) : a_typeB (6) : : Where (n) is the number of that named type. : : What I'm getting is : : a_typeA (1) : a_typeA (1) : a_typeA (1) : a_typeA (1) : a_typeA (1) : a_typeA (1) : : Anybody have any pointers on how to get what I need? (speaking strictly of : the query, of course). : : H. : : ~~ : Howard Owens : Internet Operations Coordinator : InsideVC.com/Ventura County Star : [EMAIL PROTECTED] : AIM: GoCatGo1956 : ~~ : : ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: distinct count
Ben wrote: Remember -- it's generally a bad idea to run a query selecting real data and aggregates (like count) at the same time. Then how do I get both the aggregate info and the other query columns I need for the same output? H. -Original Message- From: Ben Doom [SMTP:[EMAIL PROTECTED] Sent: Tuesday, March 11, 2003 2:22 PM To: CF-Talk Subject: RE: distinct count I think you've overcomplicated the query. IIRC, it should look something like: select count(a_items.wine_type_id) as wcount where [whatever] group by a_items.wine_type_id order by [whatever] Remember -- it's generally a bad idea to run a query selecting real data and aggregates (like count) at the same time. -- Ben Doom Programmer General Lackey Moonbow Software, Inc : -Original Message- : From: Owens, Howard [mailto:[EMAIL PROTECTED] : Sent: Tuesday, March 11, 2003 5:01 PM : To: CF-Talk : Subject: SQL: distinct count : : : The following query doesn't quite do what I want it to do, and I'm stumped : on how to get what I want ... : : SELECT COUNT(a_items.WINE_TYPE_ID) AS WCOUNT, : w_types.TYPE_ID, : w_types.TYPE, : a_items.TYPE_ID, : a_items.END_TIME : FROM w_types, a_items : WHERE w_types.TYPE_ID = a_items.TYPE_ID : AND DATEDIFF( minute, #createODBCDATETIME(dateAdd(h, -3, NOW()))#, : a_items.END_TIME) = 0 : GROUP BY w_types.TYPE_ID, : w_types.TYPE, : a_items.TYPE_ID, : a_items.END_TIME : ORDER BY TYPE : : What I need is something that returns: : : a_typeA (5) : a_typeB (6) : : Where (n) is the number of that named type. : : What I'm getting is : : a_typeA (1) : a_typeA (1) : a_typeA (1) : a_typeA (1) : a_typeA (1) : a_typeA (1) : : Anybody have any pointers on how to get what I need? (speaking strictly of : the query, of course). : : H. : ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: distinct count
Ben wrote: Remember -- it's generally a bad idea to run a query selecting real data and aggregates (like count) at the same time. Then how do I get both the aggregate info and the other query columns I need for the same output? I disagree with the statement that it's a bad idea to run a query selecting real data and aggregates at the same time - if you want to ask a question like how many sales per region, that's how you do it. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: distinct count
OK, I just realized something about this query ... I have a similar query for another part of the site that is exactly the same query, except their is no date comparison. That's why I'm not getting the results I expect (the other query works the way I expect), because since each entry has a diffferent SMALLDATETIME value, it counts as one ... now I've got to figure out a work around ... any suggestions? H. -Original Message- From: Owens, Howard [SMTP:[EMAIL PROTECTED] Sent: Tuesday, March 11, 2003 2:01 PM To: CF-Talk Subject: SQL: distinct count The following query doesn't quite do what I want it to do, and I'm stumped on how to get what I want ... SELECT COUNT(a_items.WINE_TYPE_ID) AS WCOUNT, w_types.TYPE_ID, w_types.TYPE, a_items.TYPE_ID, a_items.END_TIME FROM w_types, a_items WHERE w_types.TYPE_ID = a_items.TYPE_ID AND DATEDIFF( minute, #createODBCDATETIME(dateAdd(h, -3, NOW()))#, a_items.END_TIME) = 0 GROUP BY w_types.TYPE_ID, w_types.TYPE, a_items.TYPE_ID, a_items.END_TIME ORDER BY TYPE What I need is something that returns: a_typeA (5) a_typeB (6) Where (n) is the number of that named type. What I'm getting is a_typeA (1) a_typeA (1) a_typeA (1) a_typeA (1) a_typeA (1) a_typeA (1) Anybody have any pointers on how to get what I need? (speaking strictly of the query, of course). H. ~~ Howard Owens Internet Operations Coordinator InsideVC.com/Ventura County Star [EMAIL PROTECTED] AIM: GoCatGo1956 ~~ ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: distinct count
Nope. That wasn't it. I removed the time stuff and it didn't change the results. Yet I can't see any real other difference between the queries. H. -Original Message- From: Owens, Howard [SMTP:[EMAIL PROTECTED] Sent: Tuesday, March 11, 2003 8:09 PM To: CF-Talk Subject: RE: distinct count OK, I just realized something about this query ... I have a similar query for another part of the site that is exactly the same query, except their is no date comparison. That's why I'm not getting the results I expect (the other query works the way I expect), because since each entry has a diffferent SMALLDATETIME value, it counts as one ... now I've got to figure out a work around ... any suggestions? H. -Original Message- From: Owens, Howard [SMTP:[EMAIL PROTECTED] Sent: Tuesday, March 11, 2003 2:01 PM To: CF-Talk Subject:SQL: distinct count The following query doesn't quite do what I want it to do, and I'm stumped on how to get what I want ... SELECT COUNT(a_items.WINE_TYPE_ID) AS WCOUNT, w_types.TYPE_ID, w_types.TYPE, a_items.TYPE_ID, a_items.END_TIME FROM w_types, a_items WHERE w_types.TYPE_ID = a_items.TYPE_ID AND DATEDIFF( minute, #createODBCDATETIME(dateAdd(h, -3, NOW()))#, a_items.END_TIME) = 0 GROUP BY w_types.TYPE_ID, w_types.TYPE, a_items.TYPE_ID, a_items.END_TIME ORDER BY TYPE What I need is something that returns: a_typeA (5) a_typeB (6) Where (n) is the number of that named type. What I'm getting is a_typeA (1) a_typeA (1) a_typeA (1) a_typeA (1) a_typeA (1) a_typeA (1) Anybody have any pointers on how to get what I need? (speaking strictly of the query, of course). H. ~~ Howard Owens Internet Operations Coordinator InsideVC.com/Ventura County Star [EMAIL PROTECTED] AIM: GoCatGo1956 ~~ ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
