Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.
COURSE of the MONTH
10 days, 8 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
sql group by calculated column
Posted on 2012-12-22
Hi, how can I group by a calculated column? I am trying to do what is below and it is not working.
select
case when StartingIv >= 0 and StartingIv < 0.1 then '0-0.1'
when StartingIv >= 0.1 and StartingIv < 0.2 then '0.1-0.2'
when StartingIv >= 0.2 and StartingIv < 0.3 then '0.2-0.3'
when StartingIv >= 0.3 and StartingIv < 0.4 then '0.3-0.4'
when StartingIv >= 0.4 and StartingIv < 0.5 then '0.4-0.5'
when StartingIv >= 0.5 and StartingIv < 0.6 then '0.5-0.6'
when StartingIv >= 0.6 and StartingIv < 0.7 then '0.6-0.7'
when StartingIv >= 0.7 and StartingIv < 0.8 then '0.7-0.8'
when StartingIv >= 0.8 and StartingIv < 0.9 then '0.8-0.9'
when StartingIv >= 0.9 and StartingIv < 1 then '0.9-1'
when StartingIv >= 1 and StartingIv < 1.1 then '1-1.1'
else 'ELSE'
end as IvRange
, Profit
from Trades
group by IvRange
select
case when StartingIv >= 0 and StartingIv < 0.1 then '0-0.1'
when StartingIv >= 0.1 and StartingIv < 0.2 then '0.1-0.2'
when StartingIv >= 0.2 and StartingIv < 0.3 then '0.2-0.3'
when StartingIv >= 0.3 and StartingIv < 0.4 then '0.3-0.4'
when StartingIv >= 0.4 and StartingIv < 0.5 then '0.4-0.5'
when StartingIv >= 0.5 and StartingIv < 0.6 then '0.5-0.6'
when StartingIv >= 0.6 and StartingIv < 0.7 then '0.6-0.7'
when StartingIv >= 0.7 and StartingIv < 0.8 then '0.7-0.8'
when StartingIv >= 0.8 and StartingIv < 0.9 then '0.8-0.9'
when StartingIv >= 0.9 and StartingIv < 1 then '0.9-1'
when StartingIv >= 1 and StartingIv < 1.1 then '1-1.1'
else 'ELSE'
end as IvRange
, Profit
from Trades
group by IvRange
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
2
4 Comments
you need to write case again in group by clause:
select
case when StartingIv >= 0 and StartingIv < 0.1 then '0-0.1'
when StartingIv >= 0.1 and StartingIv < 0.2 then '0.1-0.2'
when StartingIv >= 0.2 and StartingIv < 0.3 then '0.2-0.3'
when StartingIv >= 0.3 and StartingIv < 0.4 then '0.3-0.4'
when StartingIv >= 0.4 and StartingIv < 0.5 then '0.4-0.5'
when StartingIv >= 0.5 and StartingIv < 0.6 then '0.5-0.6'
when StartingIv >= 0.6 and StartingIv < 0.7 then '0.6-0.7'
when StartingIv >= 0.7 and StartingIv < 0.8 then '0.7-0.8'
when StartingIv >= 0.8 and StartingIv < 0.9 then '0.8-0.9'
when StartingIv >= 0.9 and StartingIv < 1 then '0.9-1'
when StartingIv >= 1 and StartingIv < 1.1 then '1-1.1'
else 'ELSE'
end as IvRange
, Profit
from Trades
group by case when StartingIv >= 0 and StartingIv < 0.1 then '0-0.1'
when StartingIv >= 0.1 and StartingIv < 0.2 then '0.1-0.2'
when StartingIv >= 0.2 and StartingIv < 0.3 then '0.2-0.3'
when StartingIv >= 0.3 and StartingIv < 0.4 then '0.3-0.4'
when StartingIv >= 0.4 and StartingIv < 0.5 then '0.4-0.5'
when StartingIv >= 0.5 and StartingIv < 0.6 then '0.5-0.6'
when StartingIv >= 0.6 and StartingIv < 0.7 then '0.6-0.7'
when StartingIv >= 0.7 and StartingIv < 0.8 then '0.7-0.8'
when StartingIv >= 0.8 and StartingIv < 0.9 then '0.8-0.9'
when StartingIv >= 0.9 and StartingIv < 1 then '0.9-1'
when StartingIv >= 1 and StartingIv < 1.1 then '1-1.1'
else 'ELSE'
end
Active today
it is not clear why do you nned in this query group by" - maybe you need SELECT DISTINCT instead of group by
but you can try:
--------------------
select
a.*
FROM
(
select
case when StartingIv >= 0 and StartingIv < 0.1 then '0-0.1'
when StartingIv >= 0.1 and StartingIv < 0.2 then '0.1-0.2'
when StartingIv >= 0.2 and StartingIv < 0.3 then '0.2-0.3'
when StartingIv >= 0.3 and StartingIv < 0.4 then '0.3-0.4'
when StartingIv >= 0.4 and StartingIv < 0.5 then '0.4-0.5'
when StartingIv >= 0.5 and StartingIv < 0.6 then '0.5-0.6'
when StartingIv >= 0.6 and StartingIv < 0.7 then '0.6-0.7'
when StartingIv >= 0.7 and StartingIv < 0.8 then '0.7-0.8'
when StartingIv >= 0.8 and StartingIv < 0.9 then '0.8-0.9'
when StartingIv >= 0.9 and StartingIv < 1 then '0.9-1'
when StartingIv >= 1 and StartingIv < 1.1 then '1-1.1'
else 'ELSE'
end as IvRange
, Profit
from Trades) a
group by IvRange
but you can try:
--------------------
select
a.*
FROM
(
select
case when StartingIv >= 0 and StartingIv < 0.1 then '0-0.1'
when StartingIv >= 0.1 and StartingIv < 0.2 then '0.1-0.2'
when StartingIv >= 0.2 and StartingIv < 0.3 then '0.2-0.3'
when StartingIv >= 0.3 and StartingIv < 0.4 then '0.3-0.4'
when StartingIv >= 0.4 and StartingIv < 0.5 then '0.4-0.5'
when StartingIv >= 0.5 and StartingIv < 0.6 then '0.5-0.6'
when StartingIv >= 0.6 and StartingIv < 0.7 then '0.6-0.7'
when StartingIv >= 0.7 and StartingIv < 0.8 then '0.7-0.8'
when StartingIv >= 0.8 and StartingIv < 0.9 then '0.8-0.9'
when StartingIv >= 0.9 and StartingIv < 1 then '0.9-1'
when StartingIv >= 1 and StartingIv < 1.1 then '1-1.1'
else 'ELSE'
end as IvRange
, Profit
from Trades) a
group by IvRange
Active today
Not exactly. Any column not contained in a group by needs to have an aggregate function applied to (min, max, sum, count, avg and the like). The second suggestion has to be:
select
a.IvRange, sum(Profit) as Profit
FROM
(
select
case when StartingIv >= 0 and StartingIv < 0.1 then '0-0.1'
when StartingIv >= 0.1 and StartingIv < 0.2 then '0.1-0.2'
when StartingIv >= 0.2 and StartingIv < 0.3 then '0.2-0.3'
when StartingIv >= 0.3 and StartingIv < 0.4 then '0.3-0.4'
when StartingIv >= 0.4 and StartingIv < 0.5 then '0.4-0.5'
when StartingIv >= 0.5 and StartingIv < 0.6 then '0.5-0.6'
when StartingIv >= 0.6 and StartingIv < 0.7 then '0.6-0.7'
when StartingIv >= 0.7 and StartingIv < 0.8 then '0.7-0.8'
when StartingIv >= 0.8 and StartingIv < 0.9 then '0.8-0.9'
when StartingIv >= 0.9 and StartingIv < 1 then '0.9-1'
when StartingIv >= 1 and StartingIv < 1.1 then '1-1.1'
else 'ELSE'
end as IvRange
, Profit
from Trades) a
group by IvRange
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied?
You can use SQL Server Initialize from Backup…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses
Course of the Month10 days, 8 hours left to enroll
765 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.