Solved

sql group by calculated column

Posted on 2012-12-22

3 solutions

440 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

Comment

Question by:jackjohnson44

[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

Join The Community

4 Comments

LVL 12

Assisted Solution

by:Habib Pourfard

Habib Pourfard earned 167 total points

ID: 387164902012-12-22

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

LVL 43

Assisted Solution

by:Eugene Z

Eugene Z earned 166 total points

ID: 387165122012-12-22

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

LVL 70

Accepted Solution

by:Qlemo

Qlemo earned 167 total points

ID: 387176122012-12-23

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

LVL 43

Expert Comment

by:Eugene Z

ID: 387177662012-12-23

good one: Qlemo :)

Featured Post

Question has a verified solution.

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

Step by step guide to take SQL Server 2012 new feature Available Groups (Hadr) to Production

Article by: Duane

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…

Microsoft SQL Server

How to recover deleted rows in SQL Server

Article by: Yashwant

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.

Creating and Executing a SQL Pass-thru Query

Video by: TechMommy

Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…