How do I group by range of something that I'm calculating in my query?

Question

USE [IMTsimulations]
GO

SELECT SUM(CASE WHEN Xa BETWEEN 90 and 130 THEN 1 ELSE 0 end)/SUM(1) AS Xa_PIR2
  FROM [dbo].[Simulations]
  WHERE [dbo].[Simulations].[ExperimentID] IN (
    SELECT [ExperimentID] FROM [dbo].[Parameters] WHERE [SensorError] = 1 AND [ExogDEXCurve] <> 4 AND [ControlRange] = 2
  )
/*GROUP BY Xa_PIR2 WHERE Xa_PIR2 < .6, Xa_PIR2 BETWEEN .6 AND .8, Xa_PIR2 BETWEEN .8 AND 1 

My attempt at the group, obviously doesn't work. */
GO

I know I could do a SELECT FROM PERCENT_IN_RANGE_TABLE, kind of like they did here, but I've got no such table. I want to do this on the fly, as I'm only going to do this specific analysis once.

Ideally, I would have a percentage of experiments that fall into each "Time In Range" group. I want to know what percentage of Experiments (uniquely identified by ExperimentID) have less than 60% time in range, what percent have 60-80% time in range, and what percent have 80-100% time in range.

Can you describe what results you want and what your data looks like? — Gordon Linoff, Oct 21 '15 at 16:11
Ideally, I would have a percentage of experiments that fall into each "Time In Range" group. I want to know what percentage of Experiments (uniquely identified by ExperimentID) have less than 60% time in range, what percent have 60-80% time in range, and what percent have 80-100% time in range. edited the OP with this info. — ijustlovemath, Oct 21 '15 at 16:29

score 0 · Answer 1 · answered Oct 21 '15 at 16:12

0

Calculate your data first, using a CTE and then group it in the select

WITH calculatedSimulations
AS
(
  SELECT SUM(CASE WHEN Xa BETWEEN 90 and 130 THEN 1 ELSE 0 end)/SUM(1) AS Xa_PIR2
  FROM [dbo].[Simulations]
  WHERE [dbo].[Simulations].[ExperimentID] IN (
    SELECT [ExperimentID] FROM [dbo].[Parameters] WHERE [SensorError] = 1 AND [ExogDEXCurve] <> 4 AND [ControlRange] = 2
  )
)
SELECT Xa_PIR2, 
       COUNT(*) as count -- for example!
FROM calculatedSimulations
GROUP BY 
   CASE WHEN Xa_PIR2 < 0.6 THEN 1
   CASE WHEN Xa_PIR2 >= 0.6 AND Xa_PIR2 < 0.8 THEN 2
   CASE WHEN Xa_PIR2 >= 0.8 THEN 3
END

answered Oct 21 '15 at 16:12

Jamiec

133,658
13
134
193

I'm using SQL Server Express 2014. I had to add an ELSE after each THEN to get rid of syntax errors, but for some reason it doesn't like the END. Any idea why that may be throwing up an error? – ijustlovemath Oct 21 '15 at 16:24
I added some clarification to the OP, thanks to @Gordon Linoff – ijustlovemath Oct 21 '15 at 16:31

score 0 · Answer 2 · answered Oct 21 '15 at 16:13

Are you trying to group by a case statement?

SELECT (CASE WHEN Xa_PIR2 < 0.6 THEN 'GROUP1'
             WHEN Xa_PIR2 < 0.8 THEN 'GROUP2'
             WHEN Xa_PIR2 < 1 THEN 'GROUP3'
        END) as grp, COUNT(*) as num, AVG(Xa_PIR2) as avg_in_group
FROM (SELECT AVG(CASE WHEN s.Xa BETWEEN 90 and 130 THEN 1.0 ELSE 0 end) AS Xa_PIR2
      FROM [dbo].[Simulations] s
      WHERE s.[ExperimentID] IN 
                (SELECT p.[ExperimentID]
                 FROM [dbo].[Parameters] p
                 WHERE p.[SensorError] = 1 AND p.[ExogDEXCurve] <> 4 AND p.[ControlRange] = 2
               )
     ) s
GROUP BY (CASE WHEN Xa_PIR2 < 0.6 THEN 'GROUP1'
               WHEN Xa_PIR2 < 0.8 THEN 'GROUP2'
               WHEN Xa_PIR2 < 1 THEN 'GROUP3'
          END);

Some notes on the query:

AVG() is an aggregation function. You don't need to do two sums, unless you really, really want to.
CASE statements process the conditions in order, so you don't need to use BETWEEN.
I always put a "0" before a decimal point, to prevent unintentional errors or misreading the query.
Including the average for the group can enhance information about the distribution of the values.
Table aliases are a good idea.

Thanks for the detailed explanation of the query. When I try to run it, I get syntax errors from the CASE statement in the GROUP BY. It doesn't like the semicolon. If It helps, I'm using SQL Server Express 2014. — ijustlovemath, Oct 21 '15 at 16:17
@ijustlovemath . . . Basically, the same query runs on SQL Fiddle (http://www.sqlfiddle.com/#!6/9eecb7d/2409). — Gordon Linoff, Oct 22 '15 at 00:56
Hang on, this only returns one group. I was hoping to get results on all 3 groups. Is that possible? — ijustlovemath, Oct 22 '15 at 01:00
@ijustlovemath . . . Just add sample data for the groups you care about. — Gordon Linoff, Oct 22 '15 at 01:33

score -1 · Answer 3 · answered Oct 21 '15 at 16:11

-1

Try this sort of thing.

select this, sum(that) sumOfThat
from (
select case when whatever then 'fred' else 'barney' end this
, case when whatever then 0 else 1 end that
) temp
group by this

answered Oct 21 '15 at 16:11

Dan Bracuk

20,699
4
26
43

Could you elaborate a bit? I'm still very new to SQL. – ijustlovemath Oct 21 '15 at 16:26
You can create tables on the fly by giving an alias to a subquery. In this case I did it directly. Jamiec did the same thing using the keyword `with`. Then you write sql as if that subquery was a table. What goes in the subquery is up to you. – Dan Bracuk Oct 21 '15 at 16:44

How do I group by range of something that I'm calculating in my query?

3 Answers3