Subqueries with SELECT statement in Sqlite

Question

Subqueries with SELECT statement in Sqlite

Navigation

#1 by (3 votes)
#2 by (3 votes)

2

I need to return two values in my query and I do not know if it's possible to do this in a single select ... I wanted a better explanation to understand how to do a subselect in 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 do the query like this:

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

It just does not work, I tried to see this Tutorial and you do not understand how does.

sqlite3

asked by anonymous 17.10.2017 / 14:51

2 answers

3

The SQL below ( exactly as it 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

17.10.2017 / 14:56

get the value of a td when clicking on a checkbox of the same line tr jquery arterySignalIr / Ping - What is causing this error?

score 3 · Accepted Answer

The point is that you are creating a select based on variables created in FROM when they should be direct in 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