SQL - Using Alias in Group By

SQL - using alias in Group By

SQL is implemented as if a query was executed in the following order:

FROM clause
WHERE clause
GROUP BY clause
HAVING clause
SELECT clause
ORDER BY clause

For most relational database systems, this order explains which names (columns or aliases) are valid because they must have been introduced in a previous step.

So in Oracle and SQL Server, you cannot use a term in the GROUP BY clause that you define in the SELECT clause because the GROUP BY is executed before the SELECT clause.

There are exceptions though: MySQL and Postgres seem to have additional smartness that allows it.

Group By Alias name

You need to used the whole expression in the GROUP BY clause or just wrap the whole statement in a subquery an do the grouping on the outer statement.

The reason why you can't use ALIAS on the GROUP BY clause that is created on the same level of the SELECT statement is because the GROUP BY is executed before the SELECT clause in which the ALIAS is created.

This is the SQL Order of Operation:

FROM clause
WHERE clause
GROUP BY clause
HAVING clause
SELECT clause
ORDER BY clause

Try this query,

SELECT  COUNT(CaseId),
        DiffDate
FROM
        (
            select  CaseId,
                    DATEDIFF(day,CreateDate,LastActivityDate) AS DiffDate
            from    VW_Case_Analysis
            where   CaseStatus = 'C' and 
                    LastActivityDate between '2013-4-1 00:00:00.000' and '2013-4-30 23:59:59.000'
        ) sub
Group By DiffDate

Alias for GROUP BY clause?

Use a nested SELECT - the inner one to extract the population and generate the calculated column, and the outer one can then GROUP BY that column:

SELECT region, SUM(population) FROM (

   SELECT CASE ...

   ... AS region, population FROM population_us_states )

GROUP BY region

Alias in Group By clause - invalid identifier

In Oracle and SQL Server, you cannot use a term in the GROUP BY
clause that you define in the SELECT clause because the GROUP BY is
executed before the SELECT clause.

https://stackoverflow.com/a/3841804/6358346

The correct way:

SELECT TRUNC(months_between(sysdate, DateofBirth) / 12) AS "age"
FROM players
GROUP BY TRUNC(months_between(sysdate, DateofBirth) / 12)
HAVING COUNT(TRUNC(months_between(sysdate, DateofBirth) / 12)) > 30;

Can't use column alias in GROUP BY

As mentioned in another answer, You can not add aliases in GROUP BY but you can add aliases in ORDER BY. Also, DATE_FORMAT is MySql function. It is TO_CHAR in Oracle.

So your final query should be as following:

SELECT
    TO_CHAR(TRANS_DATE, 'YYYY-MM') AS MONTH,
    COUNTRY,
    COUNT(*) AS TRANS_COUNT,
    SUM(CASE WHEN STATE = 'approved'  THEN 1 ELSE 0 END) AS APPROVED_COUNT,
    SUM(AMOUNT) AS TRANS_TOTAL_AMOUNT,
    SUM(CASE WHEN STATE = 'approved' THEN AMOUNT ELSE 0 END) AS APPROVED_TOTAL_AMOUNT
FROM TRANSACTIONS
GROUP BY TO_CHAR(TRANS_DATE, 'YYYY-MM'), COUNTRY
ORDER BY MONTH;

How do I perform a GROUP BY on an aliased column in SQL Server?

You pass the expression you want to group by rather than the alias

SELECT       LastName + ', ' + FirstName AS 'FullName'
FROM         customers
GROUP BY      LastName + ', ' + FirstName

Group by alias (Oracle)

select
  count(count_col),
  alias_column
from
  (
  select 
    count_col, 
    (select value from....) as alias_column 
  from 
    table
  ) as inline
group by 
  alias_column

Grouping normally works if you repeat the respective expression in the GROUP BY clause. Just mentioning an alias is not possible, because the SELECT step is the last step to happen the execution of a query, grouping happens earlier, when alias names are not yet defined.

To GROUP BY the result of a sub-query, you will have to take a little detour and use an nested query, as indicated above.

How to group by multiple columns using alias in SQL Server?

For the error i think you should change

GROUP BY ShiftId
    ,Grade
    ,DryerNum
    ,Specie

GROUP BY shifttimes.shiftid
    ,gradenames.NameText
    ,DryerNum
    ,specienames.NameText

NOTE:

In your select you have ,CreatedLocal but not in your group by, you have to remove it from the select or include on the group by

SUGGESTION:

Try to use Alias and Implicit joins (having two tables in the from clause) is a deprecated syntax, and it's recommended to switch to the modern, explicit, syntax:

SELECT COUNT(*) AS PieceCount
    ,ST.shiftid AS ShiftId
    ,SN.NameText AS Specie
    ,GN.NameText AS Grade
    ,DryerNum    
FROM 
   sheets S
inner join shifttimes ST
   ON S.ShiftIndex = ST.ShiftIndex
inner join Specienames SN
   ON S.SpecieNameIndex = SN.NameIndex
inner join GradeNames GN
   ON S.gradenameindex = GN.NameIndex
WHERE 
    CreatedLocal >= '2015-04-01'
AND CreatedLocal < '2015-06-01'
GROUP BY 
     ST.shiftid
    ,GN.NameText
    ,DryerNum
    ,SN.NameText

SQL - Using Alias in Group By