Perform sub-query with data from a first

Question

Perform sub-query with data from a first

Navigation

#1 by (3 votes)

1

Hello friends, a question has arisen, where even though I have entered various forums, I have not succeeded in healing it, so see if you can give me a "light". I have a main SELECT, and some sub-queries. In one of these sub-queries I need to pass as a parameter in the WHERE clause a column that is present in the main SELECT, something like the one below.

SELECT DISTINCT ID AS NUMERO_CONTRATO,
        QUANTIDADE   = (SELECT COUNT(PARCELA) FROM 
        CONTRATO_FINANCEIRO WHERE ID = NUMERO_CONTRATO)
FROM  CONTRATO_FINANCEIRO

However, after executing this query, an error is generated because there is no field, and if I pass the ID column itself, it will not return the value of the first query.

sql sql-server

asked by anonymous 27.06.2017 / 19:14

1 answer

TimeSpan: count occurrences of a specific time convert JSON to object

score 3 · Accepted Answer

The QUANTIDADE = (subselect) stretch is wrong. And you just assign a name to the table where you are giving SELECT . For your need, the code would look like this:

SELECT 
    DISTINCT a.ID AS NUMERO_CONTRATO,
    (SELECT 
         COUNT(b.PARCELA) 
     FROM CONTRATO_FINANCEIRO b 
     WHERE b.ID = a.id) as QUANTIDADE
FROM CONTRATO_FINANCEIRO a

Or, you simply can do this, since everything is in the same table:

SELECT 
    ID AS NUMERO_CONTRATO,
    COUNT(PARCELA) as QUANTIDADE 
FROM CONTRATO_FINANCEIRO
GROUP BY ID