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.
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;
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.
Why doesn't Oracle SQL allow us to use column aliases in GROUP BY clauses?
It isn't just Oracle SQL, in fact I believe it is conforming to the ANSI SQL standard (though I don't have a reference for that). The reason is that the SELECT clause is logically processed after the GROUP BY clause, so at the time the GROUP BY is done the aliases don't yet exist.
Perhaps this somewhat ridiculous example helps clarify the issue and the ambiguity that SQL is avoiding:
SQL> select job as sal, sum(sal) as job
2 from scott.emp
3 group by job;
SAL JOB
--------- ----------
ANALYST 6000
CLERK 4150
MANAGER 8275
PRESIDENT 5000
SALESMAN 5600
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;
group by alias in Oracle
The UNION should remove any duplicates. If it doesn't then you should check the data -- maybe you have extra spaces in the text columns.
Try something like this.
select state1 as state,TRIM(desc1) as desc from table where id=X
Union
select state2 as state,TRIM(desc2) as desc from table where id=X
jOOQ - group by uses alias instead of columns
Note, for this answer, and for brevity reasons, I'm assuming you had been using the code generator. The answer is the same without code generation usage.
Why the current behaviour?
Note, this behaviour is also documented here in the manual.
In jOOQ, an aliased column expression T1.T1_ID.as("id")
can only generate 2 different versions of itself:
T1.T1_ID as ID
, i.e. the alias declaration (when inside ofSELECT
, at the top level)ID
, i.e. the alias reference (when inside of any other clause / expression than theSELECT
clause)
There isn't a third type of generated SQL that depends on the location of where you embed the alias expression, e.g. the unaliased column expression T1.T1_ID
when you put the expression in WHERE
or GROUP BY
, etc. The rationale is simple. What would a user expect when they write:
groupBy(T1.T1_ID.as("id"))
Why would they expect the as()
call to be a no-op? That would be more surprising than the status quo.
Consistency with other rendering modes
There are other types of QueryPart
in jOOQ, which have similar aliasing capabilities:
Field
Table
WindowSpecification
Parameter
CTE
Let's look at the CTE example:
Table<?> cte = name("cte").as(select(...))
That cte
reference has 2 modes of rendering itself to SQL:
- The CTE declaration (if placed in the
WITH
clause) - The CTE reference (if placed in
FROM
, etc.)
I don't think you'd expect that cte
reference to ever ignore the aliasing, and just render the SELECT
itself?
Likewise with table aliasing:
T1 x = T1.as("x");
This can render itself as:
- The alias declaration (if placed in the
FROM
clause) - The alias reference
Because the FROM
clause is logically before any other clauses, you'd never expect your x
reference to render only T1
, instead of x
or T1 as x
, right?
So, for consistency reasons across the jOOQ API, the Field
aliases must also behave like all the others.
What to do instead?
Don't re-use the alias expression outside of your SELECT
clause. Write the jOOQ SQL exactly as you'd write the actual SQL:
ctx.select(T1.T1_ID.as("id"), ...)
.from(T1)
.groupBy(T1.T1_ID)
...
Related Topics
@@Identity, Scope_Identity(), Output and Other Methods of Retrieving Last Identity
Excel Function to Make SQL-Like Queries on Worksheet Data
MySQL - How to Front Pad Zip Code with "0"
Including Null Values in an Apache Spark Join
Return Pre-Update Column Values Using SQL Only
How to Insert Multiple Records and Get the Identity Value
SQL Multiple Columns in In Clause
Hierarchical Data in Linq - Options and Performance
Custom Date/Time Formatting in SQL Server
Why Is SQL Server Throwing This Error: Cannot Insert the Value Null into Column 'Id'
Execute Stored Procedure from a Function
Oracle SQL - Identify Sequential Value Ranges
Not Null Constraint Over a Set of Columns
Join Multiple Tables with Active Records
How to Scale Pivoting in Bigquery
Why Do You Create a View in a Database