3

I've reached a point with a spreadsheet where it is no longer viable to keep data in that format. I've created a table in Libreoffice Base with the relevant information and I'm trying to put together some queries. Unfortunately, my attempts to create a SQL query are so far being met with syntax errors. To be expected, given it's all new to me.

Here's my example table:

TINYINT-A TINYINT-B NUMERIC-A NUMERIC-B BOOLEAN-A BOOLEAN-B
1 2 100 200 1 0
9 8 900 800 0 1

I have the following query running fine:

SELECT 
  SUM("TINYINT-A") AS "First Column", 
  SUM("TINYINT-B") AS "Second Column", 
  SUM("NUMERIC-A") AS "Third Column", 
  SUM("NUMERIC-B") AS "Fourth Column"
FROM 
  "Table-A"

Output would be:

First Column Second Column Third Column Fourth Column
10 10 1000 1000

I would like to add a fifth column which sums up the rows in one of the previous four column when the boolean value is equal to 1 or 0. As a separate query, I can do this:

SELECT 
  SUM("NUMERIC-A") AS "BOOLEAN-A-NUMERIC-A", 
  SUM("NUMERIC-B") AS "BOOLEAN-A-NUMERIC-B"
FROM 
  "Table-A" 
WHERE 
  "BOOLEAN-A" = 1

Expected output:

BOOLEAN-A-NUMERIC-A BOOLEAN-A-NUMERIC-B
100 200

However, if I try to put the two into one query so that the output above is tacked on to the end of the first output, I get a syntax error. This is my attempt at combining the two:

SELECT 
  (
    SELECT 
      SUM("TINYINT-A") AS "First Column", 
      SUM("TINYINT-B") AS "Second Column", 
      SUM("NUMERIC-A") AS "Third Column", 
      SUM("NUMERIC-B") AS "Fourth Column"
    FROM 
      "Table-A"
  ), 
  (
    SELECT 
      SUM("NUMERIC-A") AS "BOOLEAN-A-NUMERIC-A", 
      SUM("NUMERIC-B") AS "BOOLEAN-A-NUMERIC-B"
    FROM 
      "Table-A" 
    WHERE 
      "BOOLEAN-A" = 1
  ) 
FROM 
  "Table-A"

I forgot which SO question I tried to derive the structure of the above from, but it clearly didn't work, so either I didn't understand it correctly, or I have left out a character somewhere.

I also attempted to take the two separate queries exactly as they are, and put a new line between them with just UNION. This results in an error stating that the given command is not a SELECT statement. I'm guessing because the two statements don't have the same output structure.

I'm not even sure if the commands are the same in Base, and whether things vary significantly enough between other databases such as MySQL. I'm sure they are, and that I'm probably just doing something comparable to attempting to execute Python using HTML tags/syntax or something.

fredt
  • 24,044
  • 3
  • 40
  • 61
hiigaran
  • 285
  • 1
  • 10
  • LO Base is only the front end, not the engine. Are you using PostgreSQL for example, or an embedded setup with Firebird embedded or HSQLDB 1.8? – Jim K Aug 23 '22 at 14:22
  • I see. It does say at the bottom of the screen `HSQLDB Embedded` – hiigaran Aug 24 '22 at 02:58

1 Answers1

1

I don't know libreoffice and use Postgres, but maybe it works the same way and you can get an idea of it.

Given:

CREATE TABLE Table_A (
TINYINT_A SMALLINT,
TINYINT_B SMALLINT,
NUMERIC_A NUMERIC,
NUMERIC_B NUMERIC,
BOOLEAN_A BOOLEAN,
BOOLEAN_B BOOLEAN
);

INSERT INTO Table_A (
    TINYINT_A,
    TINYINT_B,
    NUMERIC_A,
    NUMERIC_B,
    BOOLEAN_A,
    BOOLEAN_B
)
VALUES
    (1,2,100,200,true,false),
    (9,8,900,800,false,true);

in postgres it works with subqueries like this, although I'm sure, there are better solutions:

SELECT 
    SUM(TINYINT_A) AS "First Column", 
    SUM(TINYINT_B) AS "Second Column", 
    SUM(NUMERIC_A) AS "Third Column", 
    SUM(NUMERIC_B) AS "Fourth Column",
    (SELECT SUM(NUMERIC_A) FROM Table_A WHERE BOOLEAN_A is true) AS BOOLEAN_A_NUMERIC_A,
    (SELECT SUM(NUMERIC_B) FROM Table_A WHERE BOOLEAN_A is true) AS BOOLEAN_A_NUMERIC_B
FROM Table_A
nadine
  • 92
  • 8