Q:

Apply Group By Cause on as variable Name

select CASE (id_referral) 
  WHEN 1 THEN 'Treatment' 
  WHEN 2 THEN 'Medication' 
  WHEN 3 THEN 'Medication' 
  WHEN 4 THEN 'Diagnosis / Tests' 
  WHEN 5 THEN 'Diagnosis / Tests' 
  ELSE 'Other'
  END as referralName ,
  count(id_referral) gCount 
from referralDetails
group by referralName 

I am getting error 

Invalid column name 'referralName'

A:

please tried with below query
SELECT referralName, count(id_referral) gCount
FROM (SELECT CASE (id_referral) 
  WHEN 1 THEN 'Treatment' 
  WHEN 2 THEN 'Medication' 
  WHEN 3 THEN 'Medication' 
  WHEN 4 THEN 'Diagnosis / Tests' 
  WHEN 5 THEN 'Diagnosis / Tests' 
  ELSE 'Other'
  END as referralName ,
  id_referral
from referralDetails) tbl
group by referralName 

