Column not recognized in select statement

Question

Im getting an error in the Select statement of this code.

SELECT Track.Name, Track.UnitPrice, 
    Count(*) AS Purchase_count, 
    Purchase_count * Track.UnitPrice AS Total_per_track
FROM Track
JOIN InvoiceLine ON Track.TrackId = InvoiceLine.TrackId
GROUP BY Track.Name
ORDER BY  Total_per_track desc
LIMIT 10;

The Error returning is

Result: no such column: Purchase_count
At line 1:
SELECT Track.Name, Track.UnitPrice, 
    Count(*) AS Purchase_count, 
    Purchase_count * Track.UnitPrice AS Total_per_track
    FROM Track
    JOIN InvoiceLine
    ON Track.TrackId = InvoiceLine.TrackId
    GROUP BY Track.Name
    ORDER BY  Total_per_track desc
LIMIT 10;

score 2 · Accepted Answer · answered Apr 18 '20 at 14:58

You can't refer to an alias defined in the SELECT clause in the same clause (nor in the WHERE clause for example). You need to either repeat the original expression, or use a derived table (subquery or cte).

Here the expression is simple enough so repeating seems more relevant:

SELECT 
    t.Name, 
    t.UnitPrice, 
    COUNT(*) AS Purchase_count, 
    COUNT(*) * t.UnitPrice AS Total_per_track
FROM Track t
INNER JOIN InvoiceLine il ON t.TrackId = il.TrackId
GROUP BY t.TrackId, t.Name, t.UnitPrice
ORDER BY Total_per_track desc
LIMIT 10;

Notes:

I added TrackId and UnitPrice to the GROUP BY clause; TrackId is there to avoid wrongly grouping together two tracks that would have the same Name; UnitPrice appears in the SELECT clause and is not part of an aggregate function, so it is a good practice to have it in the GROUP BY clause too (although, it does seem to be functionally dependant on TrackId)
table aliases make the query shorter to read and write

score 0 · Answer 2 · answered Apr 18 '20 at 15:03

0

In order to order by Total_per_track you need to use a table expression.

For example:

select *
from (
  SELECT
    Track.Name, Track.UnitPrice, 
    Count(*) AS Purchase_count, 
    Count(*) * Track.UnitPrice AS Total_per_track
  FROM Track
  JOIN InvoiceLine
  ON Track.TrackId = InvoiceLine.TrackId
  GROUP BY Track.Name
) x
ORDER BY Total_per_track desc
LIMIT 10

answered Apr 18 '20 at 15:03

The Impaler

45,731
9
39
76

1

@GMB You are qute right. I never noticed I could use a column alias in the ORDER BY clause. Just tested it in Oracle, DB2, PostgreSQL, MariaDB, SQL Server, MySQL, H2, HyperSQL, and Derby. In all of them it does work, and in all of them the alias cannot be used in the WHERE clause. Nice to know. Thanks. – The Impaler Apr 18 '20 at 15:13

Column not recognized in select statement

2 Answers2