How can I combine CASE and GROUP BY in Query JSON with SQL query?

Queries and Resources
,
1

I'm starting with a direct query from snowflake:

image
image693×823 31.6 KB

And I'm trying to use a switch to determine if I separate the states or not.

  • When the toggle is on, I should have the states returned and the grouping should be with the state.
  • And when it's off, STATE should be 'All' and it should group all states together.

However, as you can see, it works great when the switch is off:

image
image707×666 36.9 KB

But when the switch is on, STATE_NAME doesn't get picked up (and it still groups by them all):

image
image725×663 36.9 KB

I know I can group:

image
image686×720 30.5 KB

But check this out... when I try to rename THE_STATE, the state gets ignored and it groups the states together:

image
image741×626 30.3 KB

But there's no issue actually renaming it with the CASE statement, as long as the GROUP BY isn't involved:

image
image696×862 36.6 KB

I really need a way to do CASEs and GROUP BYs at the same time in a Query JSON with SQL query. How can I do this?

Thanks!

2

Hey @mathfour!

Doing some digging here it looks as though column aliases can't be referenced in GROUP BY clauses. You might try using something like what's mentioned here:

SELECT STATE_NAME, THE_ID, SUM(TOTAL)
FROM
  (SELECT 
     CASE WHEN {{ .. }}
       THEN THE_STATE
       ELSE 'All'
       END AS STATE_NAME, 
     THE_ID, 
     SUM(VALUE_C) AS TOTAL
   FROM {{ .. }}
   GROUP BY THE_STATE, THE_ID)
GROUP BY STATE_NAME, THE_ID

Does that work?

alias_in_group_by_clause.json (15.1 KB)

3

Ah, @Kabirdas, that does indeed work, but I left out what appears to be a pretty important bit.

I have another CASE that needs not a string (like 'All' in this situation), but a different bit from the previous query.

I've adjusted my example to include this.

Add the column THE_AREA to the original table with STX, NTX, SLA and NLA (for north and south of both states).

I need a CASE statement for when the switch is on, pick THE_STATE and when off, pick THE_AREA. Like this:

image
image483×588 24.9 KB

If I use 'All' instead of THE_AREA, your trick works. But I need the value from the other column.

Whatcha think?

4

I see! Can you try adding THE_AREA to the first GROUP_BY clause?

SELECT LOCATION, THE_ID, SUM(TOTAL)
FROM
  (SELECT 
     CASE WHEN {{ .. }}
       THEN THE_STATE
       ELSE THE_AREA
       END AS LOCATION, 
     THE_ID, 
     SUM(VALUE_C) AS TOTAL
   FROM {{ .. }}
   GROUP BY THE_AREA, THE_STATE, THE_ID)
GROUP BY LOCATION, THE_ID

alias_in_group_by_clause (1).json