?
Solved

sql group by calculated column

Posted on 2012-12-22
4
Medium Priority
?
461 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 668 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 43

Assisted Solution

by:Eugene Z
Eugene Z earned 664 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 72

Accepted Solution

by:
Qlemo earned 668 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 43

Expert Comment

by:Eugene Z
ID: 38717766
good one:  Qlemo :)
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
During the weekend, I was asked to investigate into a deadlock in SQL Server 2014. SQL being something I don’t really fancy myself being an expert at, I had to do some refreshing. This article is a collection of my notes.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…

584 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