Get Results For Most Wrong Answers

[Merle_Hanson]

I've googled this to death and can't seem to get the right output...

I can get it close with the right amount of no answers for each question...

But I can't order it correctly by most answered no...

Any help is appreciated...

Basically I want to order them by the most amount of no answers first... etc...

Coding below gets me the proper output - but not ordered by most...

Changing to order by recordcount or QCount errors...

cfquery name="allans" datasource="#ds#">
SELECT
qid, count(qid) as Qcount
FROM answer
where answer = 'no'
GROUP BY qid
ORDER BY qid
cfquery>

<cfoutput query="allans" group=qid>    
<hr>#qid# - #QCount#<br>    
</cfoutput>

ColdFusion8 - MS Access Database

Any help is appreciated thx...

[Owainnorth]

Well, take a look at your query - you're saying the value you want to order by is count(qid), is that right?

If so:

     GROUP BY count(qid) DESC

Should do you. Unless I've misunderstood?

[Merle_Hanson · Accepted solution]

Correct... Trying to order by most answered no...

No go with GROUP BY count(qid) DESC

Order By works...

ORDER BY count(qid) desc

cfquery name="allans" datasource="#ds#">
SELECT
qid, count(qid) as Qcount
FROM answer
where answer = 'no'
GROUP BY qid
ORDER BY count(qid) desc
cfquery>

<cfoutput query="allans" group=qid>    
<hr>#qid# - #QCount#<br>    
</cfoutput>

Thanx Owain... I was trying to do order by QCount...

Not sure why I didn't try that one...

Thx

[Owainnorth]

Sorry, that was a brain>fingers error, clearly order by not group by!

Glad you got it sorted.