SQL adding multiple columns

Question

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'."

ORDER BY items can't have "as". Simply do ORDER BY Total DESC. — jarlh
– jarlh, Commented Mar 14, 2019 at 9:02

Arnaud Peralta · Accepted Answer · 2019-03-14 09:03:46Z

2

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.

answered Mar 14, 2019 at 9:03

Arnaud Peralta

1,3051 gold badge17 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL adding multiple columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related