All Forums Analytics
Mooli 21 posts Joined 12/14
21 Feb 2015
Using Between with >= is this possible?

SUM (CASE WHEN d.DCG < 0.50 THEN 1 ELSE 0 END)AS "<0.5",
SUM (CASE WHEN d.DCG between >=0.50 and <1.0 THEN 1 ELSE 0 END) AS "0.5-1.0",
SUM (CASE WHEN d.DCG between >=1.0 and <2.0 THEN 1 ELSE 0 END) AS "1.0-2.0",
SUM (CASE WHEN d.DCG >=2.0 THEN 1 ELSE 0 END)AS ">2.0"
 
Tried this and getting an error: 3706 expected something between the "between" keyword and ">=".
Works fine when I remove the >= but then I'm double counting some values. Any idea whats wrong here and how to get around this so I am not ommitting  any floating decimals in my output or double counting any numbers?
 

Thanks.

Glass 225 posts Joined 04/10
23 Feb 2015

No,
Use one or the other,
Between is inclusive and can also be stated using ">= n and  <= n"
 
 
Rglass

Mooli 21 posts Joined 12/14
23 Feb 2015

Hi Glass -but then how do I eliminate double couting 1.0 or 2.0, as it would qualify for both categories.  The values are floating decimals so I need to ensure the correct values are falling into correct categories without leaving any out, or double couting. "between 1.0001 and 2.0" then "between 2.0001 and 3.0"? Was hoping for something a little cleaner and precise than that.  Any ideas?

dnoeth 4628 posts Joined 11/04
23 Feb 2015

Simply don't use BETWEEN, replace it with >= and < instead:

SUM (CASE WHEN d.DCG >=0.50 and d.DCG <1.0 THEN 1 ELSE 0 END) AS "0.5-1.0",

 

Dieter

Mooli 21 posts Joined 12/14
23 Feb 2015

Thank you Dieter that worked perfectly.

You must sign in to leave a comment.