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