2

How do i use aliases with spaces in mathematical functions and group by statements. I am able to use it if I use single word aliases, but is there any way of achieving it if the name has spaces ?

select 
   count(date_format(start_date, '%W')) AS "NUMBER OF DAYS",
   repeat('*', "NUMBER OF DAYS") 
from    ABC

I tried using double-quotes,singe-quotes and also backticks(`), but it gives an error saying "Unknown column "NUMBER OF DAYS" in field list"

dreftymac
  • 31,404
  • 26
  • 119
  • 182
Adarsh
  • 3,613
  • 2
  • 21
  • 37

2 Answers2

5

You can't reuse an alias in the select or where clause. Only in group, having, order clauses.

juergen d
  • 201,996
  • 37
  • 293
  • 362
4

You can use Derived table

select
`NUMBER OF DAYS`,
repeat('*', `NUMBER OF DAYS`) 
from
(
select 
   count(date_format(start_date, '%W')) AS `NUMBER OF DAYS`
from    ABC
) as t
Madhivanan
  • 13,470
  • 1
  • 24
  • 29