Use BETWEEN in Switch Statement-CodePudding

Trying to do a custom GROUP BY Range.

I built the following SQL Query :

SELECT TheTmp, SUM(TheTmp*0.33)
FROM MyTable
WHERE Name = 'TestName1'
GROUP BY SWITCH(TheTmp BETWEEN 0 AND 5,0,50,true)

But it throw an error :

Your query does not include the specified expression 'TheTmp' as part of an aggregate function.

'TheTmp' Is the right field since the below query works :

Select TheTmp, SUM(TheTmp*0.33)
FROM MyTable
WHERE Name = 'TestName1'
GROUP BY TheTmp

So I think that I use badely BETWEEN in my SWITCH()

For the following table :

Name        TheTmp
TestName1   8.76669265399322
TestName2   0.273812785388691
TestName1   1
TestName1   1.58938597048424

My goal is to get the output below (add value between 0 and 5 together)

TestName1   8.766*0.33
TestName1   (1.58938597048424*0.33) (1*0.33)

How do I use BETWEEN into SWITCH

SWITCH(TheTmp BETWEEN 0 AND 5,0,50,true)

CodePudding user response：

Try this:

SELECT Name, Sum(0.33*[TheTmp]) AS Amount
FROM MyTable
GROUP BY Name, IIf([TheTmp]>=0 And [TheTmp]<=5,"Group1","Group2")
HAVING (((Name)="TestName1"));

Here's the result:

Here it is for your second request:

SELECT IIf([TheTmp]>=0 And [TheTmp]<=5,"Group1","Group2") AS Grouping, Sum(0.33*[TheTmp]) AS Amount
FROM MyTable
GROUP BY IIf([TheTmp]>=0 And [TheTmp]<=5,"Group1","Group2"), Name
HAVING ((([Name])="TestName1"));

And here is request number 3:

SELECT IIf([TheTmp]>=0 And [TheTmp]<=5,"Group1","Group2") AS Grouping, Sum(0.33*[TheTmp]) AS Amount
FROM MyTable
GROUP BY IIf([TheTmp]>=0 And [TheTmp]<=5,"Group1","Group2"), Name
HAVING ((([Name])="TestName1"))
ORDER BY IIf([TheTmp]>=0 And [TheTmp]<=5,"Group1","Group2") DESC;

CodePudding user response：

Between .. And is SQL syntax. In Switch use VBA syntax:

TheTmp >= 0 And TheTmp <= 5