Help with pivot keyword (not checkbox)

I’m trying to create a table using pivot, although I’m open to other suggestions. I’ve been getting syntax errors, and I’m hoping someone can help me understand what I’m doing wrong. I’m using Metabase 0.28.6, in case it matters.

I’m working with a database where one field is a number that can be sorted into one of two categories, based on whether it’s above or below a threshold. Most of the others are booleans represented by numbers - 0 for no, 1 for yes. I want to get a report that has a column for each category, showing the total number of rows and the number of yes answers (1s) for each boolean, like this:

       Category 1     Category 2
Total      #               #
Field 2    #               #
Field 3    #               #
...

Ideally, I’d really like a third column with totals, like this:

       Category 1     Category 2     Total
Total      #               #           ##
Field 2    #               #           ##
Field 3    #               #           ##
...

But I don’t know whether that’s possible; I haven’t found a way to do it yet, even theoretically.

For now, I’m trying to get a single aggregate row to appear, just as a proof of concept. So I’m hoping for this:

       Category 1     Category 2
Field 2    #               #

As far as I can tell, to get a column for each category, I need to convert the numbers in that first field to categories, then pivot on that category and include the row count and the sum of each boolean field as aggregation functions. Since I’m going to want more than three fields, I’m not using the built-in pivot checkbox - I’m trying to use the pivot keyword in SQL. However, I’m not familiar with pivot and I keep getting syntax errors.

I don’t want to show the specific field names I’m using, but here’s a copy of what I have with placeholder names:

WITH Table1 AS (
  SELECT CASE
    WHEN CAST(field1 AS int) < 6000 THEN 'Category1'
    ELSE 'Category2'
  END AS "Category", field2
  FROM "table"
  LIMIT 2000
)
SELECT * FROM Table1
PIVOT (
  sum (field2) AS "Field 2"
  FOR Category IN ([Category1], [Category2])
)

The error I keep getting is

org.postgresql.util.PSQLException: ERROR: syntax error at or near '(' 

if that’s helpful. It seems to be near the second open-parenthesis after the PIVOT keyword. For instance, in the code above, it triggers on the open-parenthesis after “sum”. I have verified that field2 is numeric; I can sum it without trouble when I’m not trying to pivot.

Can anyone tell me what I’m doing wrong and what I should do instead? It doesn’t have to use pivot, as long as I get the results I want. If I can get a third column showing totals, that would be even better.

Hi @Amy82986
A lot of things have changed since 0.28.6, which is more than 2 years old. Latest release is 0.35.4
You should consider upgrading, but it’s going to be a big change, so make sure that you have backups.

And for general SQL query problems that are unrelated to Metabase, you’ll likely find better help on forums like stackoverflow.com

I’m not in charge of what version we’re using. I’m not really sure why we’re using such an old one, but I’m sure I don’t have the authority to upgrade it.

I’ve been looking everywhere I could for SQL help with no luck. I asked here because I was wondering if Metabase had somehow changed something - I just left a job involving Wix Corvid, and I remember how they claimed you could code in Javascript, but really they made you use their own code which was Javascript with about half the functions removed. I was wondering if Metabase had done the same thing with SQL.

What REALLY bugs me is I can create the exact table I want - aggregates, totals and everything - if I have a ROW for each category. It’s two feet wide and an inch tall, but it has all the data I need, arranged how I need it. If I could just rotate THAT table, it would be perfect! But every time I search for help rotating a table, either in Metabase or general SQL, all I get is about 90% “Oh, you mean pivot!” and 10% “It can’t be done.”

@Amy82986 You can write (almost) any SQL query and it will work as expected - Metabase just passes that along to your database, which then returns the result.

Try running your query in any other SQL client and see if it works - if it doesn’t, then it doesn’t work in Metabase either.

As for changing which column is pivoted, Metabase has a function for that, but I don’t remember if that existed back in 0.28. You can change the pivoted column in setting “Pivot column”: