What I want to do is
SELECT COUNT(CustomerID) as CC as 'Customer Count', Country FROM Customers GROUP BY Country HAVING CC > 5 ORDER BY CC DESC;
Which is wrong.
What is the right way?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
UPDATE: My original answer is below. I had completely missed the two 'AS' clauses in the original answer, and answered as if the
AS 'Customer Count' was not there.
I'm not aware of any SQL language that would let you specify multiple aliases for a column name within a given query.
And, in every SQL variation I know, the column name "alias" is only used as a display name. The only part of the actual query where you can refer to the alias rather than using the column name as defined in the original table (or, for columns that are created via an expression, the actual expression) is in the
ORDER BY clause. My understanding is that's merely because, once the
ORDER BY is being applied, the actual determination of what rows will be returned by the basic query is completed;
LIMIT may determine which rows will be seen, but which rows fit the basic criteria is known, as are their contents.
As Robert Carnegie noted, the only workaround that lets you functionally use the display name as the column in the rest of the query (
HAVING clauses, or in an expression that defines another column) is to construct a subquery in which you alias the column in question, so that in the main query you can use that alias.
To avoid using
COUNT(CustomerId) in your
HAVING clause (and
'Customer Count' in your
ORDER BY clause), you'd have to restructure the query:
SELECT CC as 'Customer Count', Country FROM (SELECT COUNT(CustomerID) as CC, Country FROM Customers GROUP BY Country ) sq WHERE CC > 5 ORDER BY CC DESC;
Given the need for the sub-query, it's not clear to me that this is any easier to follow than the more basic form of the query that would provide the same results:
SELECT COUNT(CustomerID) as 'Customer Count', Country FROM Customers GROUP BY Country HAVING COUNT(CustomerID) > 5 ORDER BY 'Customer Count' DESC;
Not sure what version of SQL you're using, but I suspect this is fairly common amongst them.
Within the context of the query, the display name of a column is ignored. You have to reference it by the name of the underlying field, or by the full calculation.
To the best of my knowledge, only the
ORDER BY clause qualifies as being outside the context of the query; there, the engine assumes you're using the display names unless you tell it otherwise Example:
SELECT t1.col1 as col1 ,t2.col1 as col2 ,t1.col27 as col3 FROM t1 INNER JOIN t2 ON t1.col1 = t2.col16 ORDER BY col1, t1.col2
In the above, the only way to sort by
t1 instead of the column with the display name
t2.col1) is to specify
So, instead of:
HAVING CC > 5
you need to use:
HAVING COUNT(CustomerID) > 5
A couple of ways to override column names - I'm away from SQL but this may work; however, I find sub-queries like this may perform poorly.
SELECT sq.cc AS [Customer Count], sq.Country FROM ( SELECT COUNT(CustomerID) AS cc, Country GROUP BY Country HAVING COUNT(CustomerID) > 5 ) sq ORDER BY sq.cc DESC
SELECT 0 AS [Customer Count], 'Canadia' AS Country WHERE ( 'Rows' = 'None' ) UNION ALL SELECT ...separate query with its own column naming...
(This has obvious problems)