SQL Aggragates and Alias's

Question

I have a query that should return a scalar value if an element is referenced in the table more than once.

SELECT 
    COUNT(*)
FROM 
    Items
WHERE
    FKID = 2003799
GROUP BY 
    FKID 
HAVING 
    COUNT(*)>1 
ORDER BY 
    COUNT(*)

Why can't I assign aggragate an alias and refer to an alias in the rest of the query instead of repeating the aggragate function?

Sort of:

SELECT 
    COUNT(*) AS CountById
FROM 
    Items
WHERE
    FKID = 2003799
GROUP BY 
    FKID 
HAVING 
    CountById>1 
ORDER BY 
    CountById

Change Is there an alternative syntax that allows you to use the same idea?

Answer 1

Since the sentence SELECTis executed after the sentence HAVING, therefore, when the engine reads the sentence HAVING, it does not yet know what it means CountById.

Answer 2

, ... SELECT ... ... . http://databases.aspfaq.com/database/how-do-i-use-a-select-list-alias-in-the-where-or-group-by-clause.html.

• FROM , , . AS , .

• WHERE , ; , TRUE ( UNKNOWN FALSE). WHERE FROM.

• GROUP BY, , . : (1) (2) (.. ) (3) (4) , .

• HAVING ; GROUP BY, .

• SELECT . , , SELECT . AS SELECT. , WHERE; SELECT WHERE cluase .

• , , C, Pascal, Algol .. , , .

Answer 3

. GROUP BY HAVING SELECT, undefined . , ORDER BY SELECT, .

From the Pinal Dave Blog , evaluation order:

• WITH
• ON
• EXTERNAL
• WHERE
• GROUP BY
• CUBE | ROLLUP
• HAVING
• SELECT
• Distinct
• TO ORDER
• TOP

Answer 4

Typically, a list SELECTis the last part of an expression that needs to be evaluated (although this is the first part!), So there is no way to use the alias defined there in other sentences of the instruction.