-1

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

  • Right after I posted a light turned on Select CC as 'Customer Count', Country From (SELECT COUNT(CustomerID) as CC, Country FROM Customers GROUP BY Country HAVING CC > 5 ORDER BY CC DESC); – David King Jun 6 '17 at 21:00
  • 5
    That is invalid standard SQL. Identifiers need to be enclosed in double quotes. Single quotes are for string values – a_horse_with_no_name Jun 6 '17 at 21:02
  • Good to know, although it did work in the test database. – David King Jun 7 '17 at 13:37
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
| improve this answer | |
0

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)

| improve this answer | |
  • So, I am going to chalk this one up to lack of formal training, and experience leading me to incorrectly frame my question. What I actually needed was simply a two word alias which I did not know was possible until I read your post. Thank you! I did not know you could simply use square brackets for an alias, and it solved all my issues. SELECT COUNT(CustomerID) as [Customer Count], Country FROM Customers GROUP BY Country HAVING [Customer Count] > 5 ORDER BY [Customer Count] DESC; – David King Jun 7 '17 at 14:55
  • Oh, and it looks like this is very much SQL version specific. I have been using WebSQL and MySQL for testing. My working environment is SQL Server. They all handle it a little differently. – David King Jun 7 '17 at 16:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.