CODEWITHSUNDEEP
mysqlsqlgroup-by
stack
stack

Reputation: 10218

How can I use aliases on GROUP BY clause?

Here is my query:

SELECT  SUM(score) score,
    type,
    context,
    post_id,
    e.table_code,
    comment_id,
    MIN(seen) seen,
    MAX(date_time) d_t,
    (CASE   WHEN FROM_UNIXTIME(MAX(date_time)) >= CURDATE() THEN 'today'
        WHEN FROM_UNIXTIME(MAX(date_time)) >= DATE_SUB(CURDATE(), INTERVAL 1 DAY) THEN 'yesterday'
        WHEN FROM_UNIXTIME(MAX(date_time)) >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) THEN 'in last week'
        ELSE 'in last month'
    END) as range_day
FROM `events` e
WHERE e.id IN ($ids)
GROUP BY type, post_id, e.table_code, comment_id, range_day
ORDER BY seen, MAX(date_time) desc, MAX(e.id) desc

It throws this error:

#1056 - Can't group on 'range_day'

And if I remove range_day from GROUP BY clause, then it works as well. But I need to also group the result also based on range_day. How can I do that?

Upvotes: 1

Views: 267

Answers (3)

Rick James
Rick James

Reputation: 142278

First, your problem...

You cannot GROUP BY an aggregate. Notice the MAX() inside range_day. Your GROUP BY should include all non-aggregate items in the SELECT. context is missing and may lead to an error in subsequent releases.

Then another problem...

         MIN(seen) seen,
         MAX(d_t) d_t,
...
ORDER BY seen,
         MAX(d_t) desc

Notice an inconsistency? An ambiguity? Is seen (in the ORDER BY) supposed to be the original seen, or the alias seen, meaning MIN(seen)? Ditto for d_t?

Always try to avoid having an alias spelled the same as a column name when you need to refer to it later. In WHERE, it must be the column name; in ORDER BY and HAVING, it is the alias.

So, I think, this is wrong: MAX(d_t) desc in the ORDER BY.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269633

Your definition of range_day doesn't exactly make sense. Why does it use MAX()? Max of what?

A natural way to make the query work is to remove the MAX() from the definition:

SELECT SUM(score) score, type, context, post_id, e.table_code, comment_id,
       MIN(seen) as seen, MAX(date_time) as d_t,
       (CASE WHEN FROM_UNIXTIME(date_time) >= CURDATE() THEN 'today'
             WHEN FROM_UNIXTIME(date_time) >= DATE_SUB(CURDATE(), INTERVAL 1 DAY) THEN 'yesterday'
             WHEN FROM_UNIXTIME(date_time) >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) THEN 'in last week'
            ELSE 'in last month'
        END) as range_day
FROM `events` e
WHERE e.id IN ($ids)
GROUP BY type, post_id, e.table_code, comment_id, range_day
ORDER BY seen, MAX(date_time) desc, MAX(e.id) desc;

More comments:

  • The IN ($ids) probably doesn't do what you expect. The variables $ids is treated as a single value, so this is equivalent to e.di = $ids.
  • If this doesn't do what you want, then you might want MAX() at some other level of aggregation. That would require an additional subquery.

Upvotes: 2

Paul Spiegel
Paul Spiegel

Reputation: 31772

Not sure what you are trying to do. But you can wrap your query into a subquery without range_day in the GROUP BY clause. Then use your GROUP BY clause in the outer query as it is.

SELECT  SUM(score) score,
    type,
    context, -- WARNING! Not listed in group by clause
    post_id,
    table_code,
    comment_id,
    MIN(seen) seen,
    MAX(d_t) d_t,
    range_day
FROM (
    SELECT  SUM(score) score,
        MAX(id) as id,
        type,
        context, -- WARNING! Not listed in group by clause
        post_id,
        e.table_code,
        comment_id,
        MIN(seen) seen,
        MAX(date_time) d_t,
        (CASE   WHEN FROM_UNIXTIME(MAX(date_time)) >= CURDATE() THEN 'today'
            WHEN FROM_UNIXTIME(MAX(date_time)) >= DATE_SUB(CURDATE(), INTERVAL 1 DAY) THEN 'yesterday'
            WHEN FROM_UNIXTIME(MAX(date_time)) >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) THEN 'in last week'
            ELSE 'in last month'
        END) as range_day
    FROM `events` e
    WHERE e.id IN ($ids)
    GROUP BY type, post_id, e.table_code, comment_id
) sub
GROUP BY type, post_id, table_code, comment_id, range_day
ORDER BY seen, MAX(d_t) desc, MAX(id) desc

However - you select context without aggregation wich is not listed in the GROUP BY clause. Thus you will get some "random" value from the group. In strict mode the query will fail.

Upvotes: 3

Related Questions