19
Views
2
Comments
SQL GROUP BY in IF()

Is it possible for us to put GROUP BY Function inside of IF() as a query in SQL?

mvp_badge
MVP
Rank: #162

Hi Wilbye,

This is not exactly an OutSystems question, this more a SQL situation.

 sure if this is the best option but you can use a case-when for that. 

In the above example you are always grouping by Year but when the timeMode is 'm' it will also group by month.

Hope this helps!

Rank: #55

Hi Willbye,


Just one add-on to Nuno's answer, you should use the syntax CASE WHEN <condition> THEN <value> ELSE <value> END make sure the <value> of both THEN and ELSE branches is the same (both have to be number or date, etc.).

You will also need to have the same CASE in the SELECT statement as you have in GROUP BY, otherwise you'll have an SQL error.

It should be something like the example below:


SELECT CASE WHEN {Client}.[IsActive] = 1 THEN 'Active' ELSE 'Inactive' END
FROM {Client}
GROUP BY CASE WHEN {Client}.[IsActive] = 1 THEN 'Active' ELSE 'Inactive' END


Regards,
João