Use SQL count(*) with an alias column

Question

Hi I'm trying to use https://data.stackexchange.com/stackoverflow/query/new to get the stack overflow gold medalists from my country. Here is my query,

SELECT TOP 1000
  ROW_NUMBER() OVER(ORDER BY Gold badges DESC) AS [#], 
  UserId AS [User Link], 
  COUNT(*) AS "Gold badges"
FROM Badges, Users
WHERE Name IN ('Copy Editor', 'Electorate', 'Famous Question', 'Fanatic', 'Great Answer', 'Great Question', 'Legendary', 'Marshal', 'Populist', 'Publicist', 'Reversal', 'Stellar Question', 'Steward', 'Unsung Hero')
AND LOWER(Location) LIKE '%sri lanka%' AND Users.Id = Badges.UserId
GROUP BY UserId
ORDER BY COUNT(*) DESC

What I need to do is to get all my countries gold medalists with gold medal amount name and row number. But I'm getting this error,

Incorrect syntax near the keyword 'DESC'.

it would be great if someone can help.

score 2 · Accepted Answer · answered Apr 21 '19 at 23:28

I think you want something like this:

SELECT TOP 1000
       ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) AS [#], 
       u.id AS User_Link, 
       COUNT(*) AS Gold_badges
FROM Badges b JOIN
     Users u
     ON u.Id = b.UserId
WHERE ?.Name IN ('Copy Editor', 'Electorate', 'Famous Question', 'Fanatic', 'Great Answer', 'Great Question', 'Legendary', 'Marshal', 'Populist', 'Publicist', 'Reversal', 'Stellar Question', 'Steward', 'Unsung Hero'
                ) AND
     LOWER(?.Location) LIKE '%sri lanka%' AND 
GROUP BY u.id
ORDER BY COUNT(*) DESC;

Notes:

The ? is for the table alias that identifies where the columns come from.
Never use commas in the FROM clause.
This fixes the join syntax to use proper, explicit, standard joins.
You cannot use table aliases anywhere else in the same SELECT. So, repeat the expression.

thanks a lot for all the knowledge bro. But unfortunately I got this error 'Incorrect syntax near '?'.' — vimuth, Apr 21 '19 at 23:40
@vimuth . . . As explained in the answer, you need to replace that with the alias of the table where the column comes from, `b` for `badges` and `u` for `users`. — Gordon Linoff, Apr 21 '19 at 23:47

Use SQL count(*) with an alias column

1 Answers1