CODEWITHSUNDEEP
sql-servergroup-byalias
Arthur Rey
Arthur Rey

Reputation: 3058

Using aliases in GROUP BY clause

The following code is working fine:

SELECT
    ISNULL(U.username, U.users_id) AS users_name,
    CONVERT(varchar(20), H.date_time, 103) AS date_time,
    SUM(sale_val) AS sales_val
FROM
    res R
        INNER JOIN invoice_hdr H ON H.guid = R.guid
        INNER JOIN users U ON U.user_no = H.user_no
GROUP BY
    ISNULL(U.username, U.users_id),
    CONVERT(varchar(20), H.date_time, 103)

But I find the GROUP BY clause redundant, and tried GROUPing BY aliases:

GROUP BY
    users_name,
    date_time

I get the following error:

Invalid column name 'users_name'

Same for date_time. Is there any way of doing it?

Upvotes: 0

Views: 55

Answers (2)

mohan111
mohan111

Reputation: 8865

SELECT RR.users_name,RR.date_time,SUM(RR.sale_val) Sale FROM 
(
SELECT
    ISNULL(U.username, U.users_id) AS users_name,
    CONVERT(varchar(20), H.date_time, 103) AS date_time,
    sale_val 
FROM
    res R
        INNER JOIN invoice_hdr H ON H.guid = R.guid
        INNER JOIN users U ON U.user_no = H.user_no

        ) RR
GROUP BY
    RR.users_name,
    CONVERT(varchar(20), RR.date_time, 103)

Upvotes: 0

Roger Wolf
Roger Wolf

Reputation: 7692

Yes, wrapping it in a subquery and performing aggregation on the outer level:

select sq.users_name, sq.date_time, sum(sq.sale_var)
from (
SELECT
    ISNULL(U.username, U.users_id) AS users_name,
    CONVERT(varchar(20), H.date_time, 103) AS date_time,
    sale_val
FROM
    res R
        INNER JOIN invoice_hdr H ON H.guid = R.guid
        INNER JOIN users U ON U.user_no = H.user_no
) sq
GROUP BY sq.users_name, sq.date_time;

ORDER BY is the only place where you can reference an alias on the same level with its definition.

Upvotes: 1

Related Questions