Perform sub-query with data from a first

Asked

Viewed 117 times

1

Hello friends, a question has arisen, where even entering several forums I did not succeed in Ana it, in this way see if they 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 parameter in the WHERE clause a column that is present in the main SELECT, something similar to what is 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 error is generated because there is no NUMERO_CONTRATO field, and if I pass the ID column itself it will not in turn with the value of the first query.

sql sql-server

  • Already tried to use JOIN?

  • Thought I will not need JOIN, to run the query it is enough only that the column (NUMERO_CONTRATO) that I pass as parameter in the WHERE clause of the sub-query is "accepted"

  • @Juniorperreiradonascimento: It was missing to relate the subconsulta with the query. This is called correlated subconsulta. // Have you evaluated Rovann’s proposal? Seems to me the solution.

1 answer

3


The excerpt QUANTIDADE = (subselect) is wrong. And you just assign a name to the table in which you are giving the 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, simply you can do so, since it is all on the same table:

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

Browser other questions tagged sql sql-server

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