CODEWITHSUNDEEP
sqlgroup-bycolumn-alias
ThatDickmanGuy
ThatDickmanGuy

Reputation: 3

Column not recognized in select statement

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;

Upvotes: 0

Views: 1277

Answers (2)

The Impaler
The Impaler

Reputation: 48770

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

Upvotes: 0

GMB
GMB

Reputation: 222432

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

Upvotes: 2

Related Questions