CODEWITHSUNDEEP
mysqlsql
Jacob
Jacob

Reputation: 920

How to reference an aliased column in where clause that contains a grouping function

I have a simple sql query below, which produces a usable result for me. However I would ideally like to further limit the results of this query to only include results where the aliased column total is less then 10. I have tried directly referencing the aliased column in a where clause, and also duplicating the COUNT() portion in a where clause, but that doesn't work. Here is the query, thanks in advance for the help.

SELECT COUNT(DISTINCT iDomainID) AS totInFile, iFileGroup
FROM Domains.`ApacheVirtualHosts`
GROUP BY iFileGroup

Upvotes: 2

Views: 94

Answers (2)

Monty Wild
Monty Wild

Reputation: 3991

You need a HAVING clause:

SELECT COUNT(DISTINCT iDomainID) AS totInFile, iFileGroup
FROM Domains.`ApacheVirtualHosts`
GROUP BY iFileGroup
HAVING COUNT(DISTINCT iDomainID) < 10

Upvotes: 2

Taryn
Taryn

Reputation: 247690

You can further filter using a HAVING clause:

SELECT COUNT(DISTINCT iDomainID) AS totInFile, iFileGroup
FROM Domains.`ApacheVirtualHosts`
GROUP BY iFileGroup
HAVING COUNT(DISTINCT iDomainID) < 10;

A HAVING clause will allow you to filter data using an aggregate function.

Upvotes: 4

Related Questions