Group By concatenated field in cfquery

Question

SELECT B_Model,B_Year, P_Brand, P_Pitch, count(P_Pitch)as Recordcount,
(P_Brand+' '+ P_Pitch)as Prop
FROM dbo.User_Record
WHERE B_Model = 'COUGAR FTD'
AND
B_Year = '2010'
GROUP BY Prop, P_Brand,P_Pitch,B_Model,B_Year
ORDER BY recordcount DESC

I can't seem to get this query to recognize Prop as a valid field for group by. Any help would be appreciated.

P_Brand and P_Pitch are both nvarchar fields.

Thanks, Allen

ilssac · Answer

IIRC you can't use alaises in the group by clause.

I've always had to do it this way.


SELECT B_Model,B_Year, P_Brand, P_Pitch, count(P_Pitch)as Recordcount,
(P_Brand+' '+ P_Pitch)as Prop
FROM dbo.User_Record
WHERE B_Model = 'COUGAR FTD' 
AND
B_Year = '2010' 
GROUP BY (P_Brand+' '+ P_Pitch), P_Brand,P_Pitch,B_Model,B_Year
ORDER BY recordcount DESC

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded