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.
