'IN' operator taking a list of arguments


#1

The following SQL syntax is a valid one.

SELECT
  a.state,
  sum(b.num_cars) * 10000.0/sum(a.data_2015) as proportion
FROM population as a
INNER JOIN "all" AS b
  ON
    a.state = b.state
WHERE a.state IN ('CA', 'TX', 'AZ')
GROUP BY
  a.state
ORDER BY
  proportion ASC

How can I achieve the following?

SELECT
  a.state,
  sum(b.num_cars) * 10000.0/sum(a.data_2015) as proportion
FROM population as a
INNER JOIN "all" AS b
  ON
    a.state = b.state
[[ WHERE a.state IN ({{state}}) ]]
GROUP BY
  a.state
ORDER BY
  proportion ASC

The focus is on the optional WHERE statement that should take a list of states (arguments).

Thank you.


WIKI: query issues (SQL/others)
#2

Nope, doesn’t work. I had the same problem. I’ve now changed my dashboards from custom SQL to using views.
Views also make code reuse easier. If you’re not able to create views in your database due to security, take a look at:


#3

@miya to achieve a similar result, you just need to add a normal field filter to your question. The lastest versions of Metabase already offer multiple selection filter.

So, instead of

SELECT
  a.state,
  sum(b.num_cars) * 10000.0/sum(a.data_2015) as proportion
FROM population as a
INNER JOIN "all" AS b
  ON
    a.state = b.state
[[ WHERE a.state IN ({{state}}) ]]
GROUP BY
  a.state
ORDER BY
  proportion ASC

just use

SELECT
  a.state,
  sum(b.num_cars) * 10000.0/sum(a.data_2015) as proportion
FROM population as a
INNER JOIN "all" AS b
  ON
    a.state = b.state
WHERE 1=1 
   [[ AND ({{state}}) ]]
GROUP BY
  a.state
ORDER BY
  proportion ASC

#4

I’ve just realised where I’ve been going wrong! I’ve been using:
image
when I should have been using:
image

First option is just a straight text substitution whereas the 2nd does the correct SQL.