| Posted By |
Discussion Topic: Cfquery Help!
-- page:
1
2
|
|
Eire |
08-04-2005 @ 12:44 PM |
|
|
Senior Member
Posts: 229
Joined: Jul 2004
|
Hi All
I have a table with a field called M1 what I want to do is if a persons name is listed in that field more than once, display the persons name and out put a number to reflet the amount of times this person is listed in this field.
I haven't a clue how I would write this queary, anyone point me in the right direction.
Thanks
"Up The Dubs"
|
|
Lola |
|
08-04-2005 @ 12:56 PM |
|
|
Senior Member
Posts: 363
Joined: Feb 2005
|
You mean, inside a column right? Try this, tell me if an error occurs:
<cfquery name='query' datasource='datasource'>
SELECT
M1,
COUNT(M1) total
FROM
table
GROUP BY
M1;
</cfquery>
<cfloop query='variables.query'>
Count: #variables.query.total#, Name: #variables.query.m1#<br />
</cfloop>
That should do the job.
This message was edited by Lola on 8-4-05 @ 10:01 PM
|
|
Eire |
|
08-04-2005 @ 1:26 PM |
|
|
Senior Member
Posts: 229
Joined: Jul 2004
|
Hi Lola
I tried it and got this
Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'COUNT(M1) total'.
The error occurred in: line 1
1 : <cfquery name='ClubResults' datasource='Pnet'>
2 : SELECT M1, COUNT(M1) total
3 : FROM club_results
This is how I have it placed
<cfquery name='ClubResults' datasource='Pnet'>
SELECT M1, COUNT(M1) total
FROM club_results
GROUP BY M1;
</cfquery><cfloop query='#variables.ClubResults#'>
Count: #variables.ClubResults.total#, Name: #variables.ClubResults.m1#<br />
</cfloop>
"Up The Dubs"
|
|
Lola |
|
08-04-2005 @ 1:35 PM |
|
|
Senior Member
Posts: 363
Joined: Feb 2005
|
Oh, probably because Microsoft Access needs an AS, unlike MySQL. Try this:
<cfquery name='ClubResults' datasource='Pnet'>
SELECT M1, COUNT(M1) as total
FROM club_results
GROUP BY M1;
</cfquery>
|
|
Eire |
|
08-04-2005 @ 1:58 PM |
|
|
Senior Member
Posts: 229
Joined: Jul 2004
|
I tried it and got
Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.
The error occurred in : line 1
1 : <cfquery name='ClubResults' datasource='Pnet'>
2 : SELECT M1, COUNT(M1) as total
3 : FROM club_results
"Up The Dubs"
|
|
Lola |
|
08-04-2005 @ 2:10 PM |
|
|
Senior Member
Posts: 363
Joined: Feb 2005
|
I just tried it on MySQL and it works, but I have never used Access so I have no clue what is wrong, try this, without the colon at the very end.
<cfquery name="ClubResults" datasource="Pnet">
SELECT COUNT(M1) as total, M1
FROM club_results
GROUP BY M1
</cfquery>
Oh, and if you get an error, post the entire error here.
This message was edited by Lola on 8-4-05 @ 2:13 PM
|
|
Eire |
|
08-04-2005 @ 2:24 PM |
|
|
Senior Member
Posts: 229
Joined: Jul 2004
|
Lola!
Yes I'm still getting the
Too few parameters. Expected 1. error.
Thank you so much for patience and postings, I will just potter around with it for a bit and see if I can pin it down.
Again thank you!!
Much appreciated!
"Up The Dubs"
|
|
swerve |
|
08-04-2005 @ 3:24 PM |
|
|
New Member
Posts: 9
Joined: Jun 2005
|
Can't you loop the query and then treat the person's name as a list delimited by spaces?
<cfquery name="query1">
select m1 from table
order by m1
</cfquery>
<cfloop query="query1">
<cfset m1_length = listlen(query1.m1," ")>
<cfif m1_length gt 1>
#query.m1# - #m1_length#<br>
</cfif>
</cfloop>
I think that should work. I think fooling around with a loop is easier than fooling around with SQL.
|
|
CJ |
|
08-04-2005 @ 4:03 PM |
|
|
Administrator
Posts: 4262
Joined: Oct 2002
|
swerve -
erm...no, that's a bad idea.
general rule of thumb is let the database do the work.
it's MUCH more efficient to write a SQL statement using a built in SQL function like COUNT() than it is to loop over 'n' number of records in a ColdFusion query, performing conditional comparisons, variable assignments, function calls, etc for each record.
---------------------------------------------------
Unfortunately Eire..i'm not sure what's wrong with your query. that looks like the syntax is right.
my only suggestion (since i don't have Access in front of me either) is to plug that query into Access' query builder and see if Access might be a little more specific about what the problem is than CF is being.
-CJ-
@ #coldfusion/DALNet
http://charlie.griefer.com
|
|
Lola |
|
08-04-2005 @ 4:10 PM |
|
|
Senior Member
Posts: 363
Joined: Feb 2005
|
It had me thinking MS Access didn't have a COUNT() function or something lol. Eire, too few parameters usually means you are passing some type of field there, and you are missing a quote, or have an unescaped quote in a variable you might be passing.
If you want, you can post the entire query you are using, go the administrator, turn on detailed error messages, and then copy and paste the entire error message from top to bottom. That way we will know exactly what it is, that may be the problem.
|
