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