How to properly sort CFQuery results-CodePudding

I have a query that selects all records from table 'ticket_folloup' for users who are marked active in table 'users'. After retrieving those results I am trying to count up each user's total records count and sort by the recordcount DESC. Whenever I try to apply the SORT BY command I receive an error:

string Can't group on 'requested_count'

I can group by a.DisplayName without receiving any errors.

What am I doing wrong?

<cfquery name="get_followups" datasource="#datasource#">
    SELECT
        a.DisplayName
        , count(*) AS requested_count
    FROM 
        users AS a 
    INNER JOIN 
        ticket_followup b ON b.requested_by = a.displayName 
    WHERE 
        a.active = 1
    GROUP BY 
        requested_count
    
</cfquery>
    
<cfoutput query="get_followups">
    <tr>
        <td>#get_followups.displayName#</td>
        <td>#get_followups.requested_count# - 
      #get_followups.recordcount#</td>      
    </tr>

CodePudding user response：

Your sql has three problems. First, you are grouping by an aggregate, count(*) as requested_count. You should be grouping by `display_name'.

Second, your query does not have an order by clause which is what you need to sort the results.

Finally, joining by the display_name could cause problems if you have more than one person with the same name.

CodePudding user response：

Presuming that a.displayName is a unique field like a userid, you will need to change your GROUP BY from requested_count to a.DisplayName. Secondly, you will need to add your ORDER BY clause after the GROUP BY clause.

<cfquery name="get_followups" datasource="#datasource#">
    SELECT
        a.DisplayName
        , count(*) AS requested_count
    FROM 
        users AS a 
    INNER JOIN 
        ticket_followup b ON b.requested_by = a.displayName 
    WHERE 
        a.active = 1
    GROUP BY 
        a.DisplayName
    ORDER BY
        requested_count
</cfquery>
                        
<cfoutput query="get_followups">
    <tr>
        <td>#get_followups.displayName#</td>
        <td>#get_followups.requested_count# - 
            #get_followups.recordcount#</td>      
    </tr>
</cfoutput>