Search code examples
mysqlaggregatealiashaving

How to use an ALIAS in a MYSQL query with aggregate function

I have a working query that returns a single value:

SELECT SUM(Stunden) AS sumFixstunden
FROM arbeitszeit
WHERE MONTH(Datum) = 3

Now I wanted to change that query using an ALIAS because it would be better for my development environment. I know, that the following query cannot work, using WHERE

SELECT SUM(Stunden) AS sumFixstunden,
MONTH(Datum) AS Monat
FROM arbeitszeit
WHERE Monat = 3

Is there a way to modify that query so that it works using an ALIAS? My attempt was:

SELECT SUM(Stunden) AS sumFixstunden,
MONTH(Datum) AS Monat
FROM arbeitszeit
HAVING Monat = 3

But this query retrieves an empty result.

I made also some attempts with a SUBQUERY but I had no success.

Solution

  • Try the following query:

    SELECT  sumFixstunden, Monat
FROM (
SELECT SUM(Stunden) AS sumFixstunden,
MONTH(Datum) AS Monat
FROM arbeitszeit group by MONTH(Datum)
) as t WHERE t.Monat = 3

    Hope this helps. Thanks.