i have a database consist of name and car name car Bill mitsubishi lancer James Lexus Bill lamborgini Bill Ferrari James Toyota Kim Ford I wanted to make an output something like this Bill (3) James (2) Kim (1) where the numbers are the total of car that the particular person owns. Tried several times and can't get them right. Please do help
Here is an example that I have used it past that does what you need coming from MSSQL. Getting Group By Count In Left Outer Join
In coldfusion this is a BREEZE You may want to modify the query to your likeing to pull the record(s) <cfquery datasource="datasource" name="qName"> SELECT * FROM [table] WHERE [field] = <cfqueryparam value="#name#">; </cfquery> Then in order to to display the information that you pulled <cfoutput> #qName.name# (#qName.recordcount#) </cfoutput> That should work. I assumed that name was set to bill and the others I have no idea but what you wanted is above you will have to modify it to be able to work with your database but thats simple stuff </cfoutput>
@FCM, No, I think you've misunderstood what the OP is trying to do. Yes, that would work .. but only if they did separate queries for each item. Obviously that's not very effecient. Especially as you can do it in one query. So JasonBartholme's post is the way to go.
I'm sorry cfStarlight your right there is only one way to do it But I have to disagree "respectfully" of course. I guess I should have told him more things to include such as having it in a cfloop and pulling the unique names prior to grabbing the data but the only thing I wanted to give him was the recordcount which is what he wanted.