CODEWITHSUNDEEP
sqlfunctionaggregate
Shinji-san
Shinji-san

Reputation: 1001

Naming Columns in SQL with AS using aggregate functions

So this is my initial query which worked:

DECLARE @total AS FLOAT;
SET @total =(SELECT COUNT(*) FROM Crimes)

SELECT
    Areas.Area,
    Areas.AreaName,
    COUNT(*) AS Total, 
    Round(CONVERT(float,(COUNT(*) / @total) * 100),3)
FROM Areas
INNER JOIN Crimes
    ON(Areas.Area = Crimes.Area)
GROUP BY
    Areas.AreaName,
    Areas.Area

and the only change I made was renaming the aggregate function to "Percent", and it's saying that there's an issue with the two columns Area and AreaName that they cannot be bound, and I'm not sure why.

New Change:

DECLARE @total AS FLOAT;
SET @total =(SELECT COUNT(*) FROM Crimes)
SELECT
    Areas.Area,
    Areas.AreaName,
    COUNT(*) AS Total,
    Round(CONVERT(float,(COUNT(*) / @total) * 100),3) AS Percent
FROM Areas
INNER JOIN Crimes
    ON(Areas.Area = Crimes.Area)
GROUP BY
    Areas.AreaName,
    Areas.Area

Upvotes: 0

Views: 2424

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521944

PERCENT is a keyword in SQL Server. Try using some other alias, e.g. Pct:

SELECT
    a.Area,
    a.AreaName,
    COUNT(*) AS Total,
    Round(CONVERT(float, (COUNT(*) / @total) * 100), 3) AS Pct
FROM Areas a
INNER JOIN Crimes c
    ON a.Area = c.Area
GROUP BY
    a.AreaName,
    a.Area;

Note that I have introduced table aliases into your query as well, which makes the code less verbose and easier to read.

You could have also used [Percent] in square brackets. But, this opens the risk that someone subquerying your query would then also have to escape this alias. So, I prefer avoiding using keywords for column and table names altogether.

Upvotes: 2

Related Questions