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=subscribe&forumid=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
