Solved

sql group by calculated column

Posted on 2012-12-22
4
438 Views
Last Modified: 2012-12-23
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
0
Comment
Question by:jackjohnson44
  • 2
4 Comments
 
LVL 12

Assisted Solution

by:Habib Pourfard
Habib Pourfard earned 167 total points
ID: 38716490
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

Open in new window

0
 
LVL 42

Assisted Solution

by:EugeneZ
EugeneZ earned 166 total points
ID: 38716512
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
0
 
LVL 69

Accepted Solution

by:
Qlemo earned 167 total points
ID: 38717612
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

Open in new window

0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 38717766
good one:  Qlemo :)
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Acces SQL Insert Problem 6 31
Where is the Help Section? 8 25
SQL Server Shrink hurting performance? 4 18
SQL Database Restore 2008 R2 1 11
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question