WIKI: query issues (SQL/others)

[jornh]

OK - here the first wiki for a category: query issues (SQL/others) as suggested by @EvaS here: Increase number of categories on forum? - #2 by EvaS

@evaS: thanks @jornh! As soon as I find some time I might start with describing how filters work as I think this is something a lot of new users struggle with.

@jornh: OK cool don't forget to point to that people should also Read The Fine Manual where appropriate. But I guess if you feel like sharing and adding more tutorial style or tips&tricks info a lot of peeps would appreciate that very much. (Myself as a relative SQL noob including) ... and remember do it on your own time - as you feel like it - don't feel pressure - ever!

The totally awesome beginners guide to how filters in Metabase work

TODO

apart from the info on filters in the user guide .. here are a few things that we learned the hard way over time worth sharing ....

it might be obvious but there is a different syntax you need to use depending on what filter you want to create:
Field filters: AND {{variable}}
Text filters: AND field_name = {{variable}}

SQL tips&tricks solutions to overcome or work around current limitations:

How to link Dashboards - workaround:

Ability to Link Dashboards (Eg. User list dash -> click ID -> user dash for that id)

In a dashboard there is a question that returns a tabular view of users. I would like to be able to click a row of this and for it to take the ID and load up the single user dashboard with that ID. Is this possible - if not is this a feature that is being worked on because it would be great. (Also as a side note - can we expect the ability to create questions with python/javascript/java etc for more complex questions in the future?)

When using filed filters in a sql query with joined tables, you can´t use table aliases:

Variables Field Filter

Hi, I’ve this query: SELECT c.business_name,b.lastname, b.firstname,(a.tot_time*b.costo_orario_ordi) as Costo FROM db_summary_syn a LEFT join users b ON a.codfis=b.codfis LEFT join db_companies c ON a.idcompany=c.id WHERE b.onoff=1 [[ AND {{v_lastname}}]] [[ AND {{v_firstname}}]] [[ AND a.dataora={{v_dataora}}]] ORDER BY c.business_name,b.lastname, a.dataora v_lastname and v_firstname are linked to users.firstname and users.lastname. When I select a value from this two value and run qu…

Building a SQL Syntax with rolling last 12 months:

Rolling Sums in Metabase

Hi Jarry, I might not understand your question right - what are you trying to do exactly? With the SQL editor you are able to do flexible dates like And str_to_date(concat(date_format(table.created_At, ‘%Y-%m’), ‘-01’), ‘%Y-%m-%d’) BETWEEN str_to_date(concat(date_format(date_add(now(), INTERVAL -12 month), ‘%Y-%m’), ‘-01’), ‘%Y-%m-%d’) AND str_to_date(concat(date_format(date_add(now(), INTERVAL -1 month), ‘%Y-%m’), ‘-01’), ‘%Y-%m-%d’) what gives you always (as an example) the last 12 months …

Compare values from two time periods:

This month VS Last month graph?

How can I compare my this month's transactions with the last month's transactions? I've created 2 questions to get the number of transactions this month and last month, when putting then together in my dashboard, I was expecting to see the bars for July 1st and August 1st (and all the other days) beside to each other, for comparison. This is the result I got, the whole July's transactions in green and this month's transactions in blue. This is essential in many reporting when comparing week on …

Using 2 databases in the same question

Multiple databases in one question without a data warehouse- YES YOU CAN!

I've been meaning to test this idea for a while, finally found the time today. If there's enough interest and people struggle with this, I'll do a proper 'How To'. First the bad news (for some of you). You need Windows. Get over it! I'm not including details of any security settings to keep stuff clearer. Install SQL Server Express (2016 or 2017). This is the free edition, only limitation is the database size of 10GB. As we're not storing any data, that's not a …

how to created views without admin permissions

More SQL - how to create a view when you don't have admin rights

I’ve found that in 0.28 I’m limited when I use SQL Queries as it prevents me using multiple values in a filter. My solution so far has been to just use a view. Then I realised that the customer I’m working for is one of the few who would allow me to create views on their database, so had to come up with a new solution for future use. Answer is to use SQL Server Express with linked servers. I can then create views of the ‘proper’ database within a new database in Expess. On top of that, I then …

Moving all the questions in a dashboard down to make empty space at top

Dashboard Arrangements/Amendment

Hi, I have a dashboard that contains 20 different charts in it - now the day has come I need to add Chart 21 and due to the layout, I need this to be at the top or 2nd top of my dashboard. It seems the only way I can do this is to manually edit the dashboard and drag every single chart down accordingly. Am I missing something obvious that allows me to insert (and also delete) blank rows into a dashboard? Cheers Stu

You know how to express it in SQL, now how do you Meta-re-base it?

SQL's IN operator - mapped to Metabase multiselect dropdowns

'IN' operator taking a list of arguments

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.…