Using Between with >= is this possible?

Analytics
N/A

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.

4 REPLIES
N/A

Re: Using Between with >= is this possible?

No,

Use one or the other,

Between is inclusive and can also be stated using ">= n and  <= n"

Rglass

N/A

Re: Using Between with >= is this possible?

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?

N/A

Re: Using Between with >= is this possible?

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",
N/A

Re: Using Between with >= is this possible?

Thank you Dieter that worked perfectly.