Sub-consultations with SELECT declaration in Sqlite

Asked

Viewed 230 times

3

I have to return two values in my query and do not know if it is possible to do this in a single select... I wanted a better explanation to understand how to make a subselect on Sqlite.

I have the following fields:

CREATE TABLE [LANCAMENTO](
    [ID] INTEGER PRIMARY KEY ASC AUTOINCREMENT NOT NULL UNIQUE, 
    [VALOR] DECIMAL(8, 2), 
    [DATE_BUY] DATETIME, 
    [DESCRIPTION] VARCHAR(150), 
    [ID_FORMA] INTEGER NOT NULL REFERENCES FORMAS([ID]), 
    [ID_CATAO] INTEGER, 
    [ID_CATEGORIA] INTEGER REFERENCES CATEGORIA([ID]), 
    [TYPE_RELEASE] VARCHAR(1), 
    [ORGANIZATION] VARCHAR(50), 
    [ID_USUARIO] INTEGER NOT NULL REFERENCES USUARIOS([ID]));

I tried to make the appointment so:

SELECT
  RESULT_D.VALOR AS VALOR_D,
  RESULT_C.VALOR AS VALOR_C
FROM
  (select sum(VALOR) form LANCAMENTO where  TYPE_RELEASE ='D') as RESULT_D,
  (select sum(VALOR) form LANCAMENTO where  TYPE_RELEASE ='C') as RESULT_C

Only it doesn’t work I tried to see in this Tutorial and doesn’t understand how to do it.

sqlite3

2 answers

4


The point is that you are creating a select based on variables created in FROM when it should be direct in the SELECT.

Change your query to.

SELECT
 (select sum(VALOR) form LANCAMENTO where  TYPE_RELEASE ='D') as VALOR_D,
 (select sum(VALOR) form LANCAMENTO where  TYPE_RELEASE ='C') as VALOR_C

or 

SELECT
  saida.RESULT_D AS VALOR_D,
  saida.RESULT_C AS VALOR_C
FROM
(
  (select sum(VALOR) form LANCAMENTO where  TYPE_RELEASE ='D') as RESULT_D,
  (select sum(VALOR) form LANCAMENTO where  TYPE_RELEASE ='C') as RESULT_C
)saida
  • 1

    Didn’t work last time...

  • 1

    There is no forehead here, certainly this missing something, what was the mistake ? but for the first query performance is cleaner and better.

  • 1

    The first one you posted worked, only the last one not so I’ll stick with the first one put already solve the problem. The error you gave in the second query was this near "LANCAMENTO": syntax error SQL Statement: SELECT output.RESULT_D AS VALOR_D, output.RESULT_C AS VALOR_C FROM (...

3

The SQL down below (exactly as is) already brings the result you want:

SELECT
  (select sum(VALOR) from LANCAMENTO where  TYPE_RELEASE ='D') as RESULT_D,
  (select sum(VALOR) from LANCAMENTO where  TYPE_RELEASE ='C') as RESULT_C

Browser other questions tagged sqlite3

You are not signed in. Login or sign up in order to post.