Possible to Group By on a function?

[LucieLastic]

hi

I'm trying to get the tail end of ICValue and then group by the tail end and PrefDisp fields. Is it possible? I have this at the moment which I was hoping would work. Ultimately, I want to extend it to get me the count of each 'tailend' for each PrefDisp.

select  PrefDisp, ICValue, substring(ICValue, charindex('=', ICValue), 10) as tail 
from RICData
where LastUpdated >= getdate()-14
group by PrefDisp, tail --substring(RICValue, charindex('=', RICValue), 10)

Many thanks for help
Lou

 

[drlex]

Yes, but you can't use the column alias ('tail' in your example), due to the order in which SQL Server processes the query; it has to be a repeat of the column calculation.

soi là, soi carré

 

[r937]

if you got an error message on the GROUP BY, it wasn't the tail alias, it was because ICValue was missing from it

your choices are to add it to the GROUP BY...

SELECT PrefDisp
     , ICValue
     , SUBSTRING(ICValue, CHARINDEX('=', ICValue), 10) AS tail 
  FROM RICData
 WHERE LastUpdated >= GETDATE()-14
 GROUP 
    BY PrefDisp
     [red], ICValue[/red]
     , tail

or else apply an aggregate function to it...

SELECT PrefDisp
     [red], MAX(ICValue) AS max_IC[/red]
     , SUBSTRING(ICValue, CHARINDEX('=', ICValue), 10) as tail 
  FROM RICData
 WHERE LastUpdated >= GETDATE()-14
 GROUP 
     by PrefDisp
      , tail

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[LucieLastic]

Thank you for your help guys. I have managed to do what I wanted, thanks to you all. I shouldn't have had the ICValue field in the select bit which I hadn't noticed initially. This is my solution.

select  PREF_DISP, 
  substring(ICValue, charindex('=', ICValue), 10), 
  count(substring(ICValue, charindex('=', ICValue), 10))
from RICData
where LastUpdated >= getdate()-14
  and ICValue like '%=%'
group by PREF_DISP, substring(ICValue, charindex('=', ICValue), 10) 
ORDER BY PREF_DISP, substring(ICValue, charindex('=', ICValue), 10)