For a query can you have a two word alias

Question

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?

From: W3schools.com

Answer 1

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; TOP or 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 (WHERE, GROUP BY, 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;

---

ORIGINAL ANSWER:

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 col2 from t1 instead of the column with the display name col2 (originally t2.col1) is to specify t1.

So, instead of:

HAVING CC > 5

you need to use:

HAVING COUNT(CustomerID) > 5

Answer 2

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

Alternatively,

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)