Solved

sql group by calculated column

Posted on 2012-12-22
4
426 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 68

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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Temporarily disable SQL Replication 7 23
SQL Help - 12 42
When to use an Aggregate Function. 18 38
Test a query 23 19
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

747 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now