Group By concatenated field in cfquery

[Guest]

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]

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

[Guest]

Thanks, I haven't coded in a while, and just plane forgot about the alias.

I just fixed the form, and loaded the value directly to a Prop field.

Thnaks, for the quick help.

Allen

Message was edited by: DFR

[Dan_Bracuk]

Regarding grouping by aliases, that depends on the database.  At, least one, redbrick, lets you do it.