CODEWITHSUNDEEP
coldfusion
user2378766
user2378766

Reputation: 13

Coldfusion 8: identify repeating values in a column and counting number of times it appears

I need to query a table in an SQL DB using CF 8, look at a specific column for repeating values, and count how many times it occurs. I am really new to CF and for the life of me can't figure out how to do this. I can query the DB and specify results from the referenced Column, but the rest is frustrating!

So, if I had the following results from the query for a specific column:

APPLE
APPLE
GRAPE
PEAR
APPLE
PEAR
APPLE

I know I could do a query of a query, but I would have to know what the values (fruits in this case) would be before hand. Or so I believe.

Basically, what I need is to be able to run the CFM and have it spit out:

Not knowing what the values might be beforehand.

Can someone please help?

Upvotes: 1

Views: 659

Answers (2)

Chris Tierney
Chris Tierney

Reputation: 1549

<cfquery name="qMyFruits">
SELECT fruitName, COUNT(fruitName) AS instances
FROM Fruits
GROUP BY fruitName
</cfquery>

<cfoutput query="qMyFruits">
<p>#qMyFruits.fruitName# : #qMyFruits.instances#</p>
</cfoutput>

Upvotes: 4

BKK
BKK

Reputation: 2073

I would do it in SQL. You could create a CFLOOP and then build a struct or other logical mechanism to count each fruit, but this is far easier:

<cfquery name="FruitSummary" datasource="myDatasource">
    SELECT
        FruitName, Count(FruitName) as Count
    FROM
        FruitBasket
    GROUP BY
        FruitName
    ORDER BY
        Count(FruitName) DESC
</cfquery>

Upvotes: 3

Related Questions