Solved

sql group by calculated column

Posted on 2012-12-22

3 solutions

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

Comment

Question by:jackjohnson44

4 Comments

LVL 12

Assisted Solution

by:Habib Pourfard

Habib Pourfard earned 668 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 664 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 72

Accepted Solution

by:Qlemo

Qlemo earned 668 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.

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 Already a member? Login.

Stellar SQL Database Toolkit is Best Solution for All SQL Database Issues

Article by: Bharat

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…

HOW TO: Fix an SQL Server Deadlock?

Article by: Avis

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.

Query Current Session Information via SQL

Video by: Steve

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

Repair Corrupt SQL Database with Stellar Phoenix SQL Database Repair Software

Video by: Bharat

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…