CODEWITHSUNDEEP
sqlvb.net
Peter James
Peter James

Reputation: 103

SQL adding multiple columns

Im trying to do a Top 10 but adding multiple columns together and grouping them by component between a certain date range, if i take the SUM's out it works like a charm, but when i put them back in i get the exception below, its been a while and i could just be having a brainfart.

sql = "SELECT TOP 10 ComponentName, (SUM(ISNULL(ulCompSizeFailure,0)) + SUM(ISNULL(ulLeadCountFailure,0)) + SUM(ISNULL(ulVPSHeightFailure,0))) as Total 
       FROM [i_import_slot_vision_errors] 
       WHERE DATE_TIME >= @startdata2 
           AND DATE_TIME < @enddata2 
       GROUP BY ComponentName 
       ORDER BY (SUM(ISNULL(ulCompSizeFailure,0)) + SUM(ISNULL(ulLeadCountFailure,0)) + SUM(ISNULL(ulVPSHeightFailure,0))) as Total DESC"

Problem is im getting an SQL exception, "Incorrect syntax near the keyword 'as'."

Upvotes: 1

Views: 52

Answers (1)

Arnaud Peralta
Arnaud Peralta

Reputation: 1305

This is a correct syntax, you can't alias an ORDER BY clause.

SELECT TOP 10 ComponentName, (SUM(ISNULL(ulCompSizeFailure,0)) + SUM(ISNULL(ulLeadCountFailure,0)) + SUM(ISNULL(ulVPSHeightFailure,0))) as Total 
FROM [i_import_slot_vision_errors] 
WHERE DATE_TIME >= @startdata2 AND DATE_TIME < @enddata2 
GROUP BY ComponentName 
ORDER BY Total DESC

Don't forget, in SQL the ORDER BY clause is read by the computer after the SELECT, you can use the alias made in the SELECT in your ORDER BY.

Upvotes: 2

Related Questions