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
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
TO
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
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
SQL GROUP BY alias
To group by a calculated column like a case statement, you have to include that statement in the group by also. For example:
SELECT
CASE ... END AS CaseStatement,
[YourColumns]
FROM YourTable
GROUP BY CASE ... END
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;
SQL - Group values with an alias
You can create groups with SQL, however the language itself cannot determine whether Lamb is meat or vegetable based on the string value of 'Lamb'. You will manually have to create the groups in a query, or add a new column with the value for easy use in your system
Related Topics
SQL Where Id in (Id1, Id2, ..., Idn)
How to Select from List of Values in Oracle
How to Deal with Concurrent Updates in Databases
Insert Update Stored Proc on SQL Server
Why Is Using '*' to Build a View Bad
Remove Duplicates Using Only a MySQL Query
Equivalent of Oracle's Rowid in SQL Server
Cannot Use Update with Output Clause When a Trigger Is on the Table
SQL - How to Select a Row Having a Column with Max Value
How to Run a Stored Procedure in SQL Server Every Hour
Solution For: Store Update, Insert, or Delete Statement Affected an Unexpected Number of Rows (0)
Get the Distinct Sum of a Joined Table Column
How to Drop SQL Default Constraint Without Knowing Its Name
How to Compare Two Dates to Find Time Difference in SQL Server 2005, Date Manipulation