Run this against the Northwind database that comes with SQL Server or modify
it to fit your needs..
SELECT
C1.CategoryName AS CATEGORY,
Count(C2.ProductID) AS 'TOTAL IN CATEGORY'
FROM
Categories C1, Products C2
WHERE
C1.CategoryID = C2.CategoryID
GROUP BY
C1.CategoryName
Mike
----- Original Message -----
From: "Les Mizzell" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Tuesday, July 29, 2003 3:08 PM
Subject: Select distinct on two fields?
> Actually a reposting/rephrase of an earlier question...
> I'm still trying to count categories..
>
>
> For each distinct instance of column A, how many distinct instances of
> column B are there...
>
> Should I use a compound query (not legal SQL):
>
> Select DISTINCT Column_A from TABLE_A
> Select DISTINCT Column_B from TABLE_A
>
> ...or a loop
>
> Query_A gets the distinct Column A records, and then:
>
> <cfloop query="query_A">
> <cfquery name="query_b">
> Select DISTINCT column_B
> WHERE column_A = "#Query_A.column_A">
> </cfquery>
>
> ...cfset record count here for each....
>
> </cfloop>
>
>
> ...or is there a much better way to handle something like this?
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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
