-1

I have the following query:

select count(*), floor(weight / 10) * 10
from patients
group by (floor(weight / 10) * 10)
order by floor(weight / 10) * 10 desc

But I want to convert it to use alias. But I know that in SQL order of operation GROUP BY is executed before SELECT which means I need to define an alias in the GROUP BY statement. But when I do I get an error. What's the SQL compliant way of doing this? This SQL goes wrong and complains of the AS usage in the GROUP BY statement:

select count(*), weight_group
from patients
group by (floor(weight / 10) * 10) AS weight_group
order by weight_group desc
Stu
  • 30,392
  • 6
  • 14
  • 33
Dan
  • 769
  • 2
  • 8
  • 23
  • 1
    You cannot use alias in the `GROUP BY`, you need to use the same expression in the `GROUP BY`. There are other way to do what you want but it is a different syntax. Look up `Derived Table`, `CTE` or `APPLY` operator. By the way, please do not spam the tag. Tag only the database that you are using. – Squirrel Aug 20 '23 at 09:37
  • To find out the database that you are using, try `select @@version;` or `select version();` – Squirrel Aug 20 '23 at 09:41
  • I've gone ahead and removed the tag spam; it is not helpful. – Thom A Aug 20 '23 at 09:45
  • 2
    Just wrap it in another select and sort it then: `SELECT * FROM (select count(*), (floor(weight / 10) * 10) as weight_group from patients group by (floor(weight / 10) * 10)) AS temp order by weight_group desc` – dognose Aug 20 '23 at 10:09
  • Thanks @Squirrel really helpful. I tried the version commands on their website but both gave errors as unrecognised. So not sure what they use. – Dan Aug 20 '23 at 10:10
  • Thanks @dognose very nice. But that still takes the mysql and postgres way and won't work in say oracle as it's defining the weight_group variable in the select portion, which in order of execution won't get evaluated in Oracle – Dan Aug 21 '23 at 07:16

0 Answers0