LibreOffice Base - joint variable calculation using SQL

I have a table with 3 columns, for example, Food, Type, and Taste.

Column Food can have anything
Column Type may have 'Fruit' or 'Vegetable' as possible values
Column Taste may have 'Bad', 'Good' or 'Delicious' as possible values

I am trying to get (with no luck so far) a SQL statement that would produce a table like:

            Bad       Good    Delicious   Total
Fruit        05         09           16      20
Vegetables   12         20           03      35
Total        17         29           19      55

Were the numbers on the output table are the COUNT of how many Food are in the table for each Type / Taste combination.

How can that be achieved?

Solution

The first two lines can be done in one query with a subquery clause:

SELECT X."Type", 
       SUM(X."Bad") AS "Bad",
       SUM(X."Good") AS "Good",
       SUM(X."Delicious") AS "Delicious",
       SUM(X."Total") AS "Total" 
FROM (SELECT "Type",
          CASEWHEN("Taste" = 'Bad',1,0) AS "Bad",
          CASEWHEN("Taste" = 'Good',1,0) AS "Good",
          CASEWHEN("Taste" = 'Delicious',1,0) AS "Delicious",
          1 AS "Total" FROM "YourTableName") X 
GROUP BY "Type"

The subquery creates the columns "Bad", "Good" and "Delicious" with a 1 or 0 in each column, then the outer part of the query adds up all the values.

To get the totals row at the bottom, you have to use UNION ALL which is not supported by the Base parser. For this query to work you will need to turn on the Edit→Run SQL Directly option. (This will disable certain form and report functionality that requires the parser - it may not matter for your usage, but just FYI for if you later use this query in a form or report.)

The query you will UNION ALL with is basically the same query, you just don't group by the types. So with the totals:

SELECT X."Type", 
       SUM(X."Bad") AS "Bad",
       SUM(X."Good") AS "Good",
       SUM(X."Delicious") AS "Delicious",
       SUM(X."Total") AS "Total" 
FROM (SELECT "Type",
          CASEWHEN("Taste" = 'Bad',1,0) AS "Bad",
          CASEWHEN("Taste" = 'Good',1,0) AS "Good",
          CASEWHEN("Taste" = 'Delicious',1,0) AS "Delicious",
          1 AS "Total" FROM "YourTableName") X 
GROUP BY "Type"

UNION ALL

SELECT 'Total' AS "BottomTotal",
       SUM(Y."Bad"),
       SUM(Y."Good"),
       SUM(Y."Delicious"),
       SUM(Y."Total") 
FROM (SELECT CASEWHEN("Taste" = 'Bad',1,0) AS "Bad",
          CASEWHEN("Taste" = 'Good',1,0) AS "Good",
          CASEWHEN("Taste" = 'Delicious',1,0) AS "Delicious",
          1 AS "Total" FROM "YourTableName") Y 
GROUP BY "BottomTotal"

Make sure to replace "YourTableName" with your actual table name throughout.