GROUP BY in query with column by subquery

Navigation
3

I have the following select: 

SELECT A.CARRO,
(SELECT TOP 1 VALOR FROM tab2 B WHERE B.DATA <= A.DATA ORDER BY B.DATA DESC) VALOR
FROM tab1 A
  

In (SELECT TOP 1 VALOR FROM tab2 B WHERE B.DATA <= A.DATA ORDER BY B.DATA DESC) will bring me the VALOR for that DATA or the%   "latest".

I have tried to do a% of% by% with%, several forms, one of them: 

SELECT A.CARRO,
SUM((SELECT TOP 1 VALOR FROM tab2 B WHERE B.DATA <= A.DATA )) VALOR
FROM tab1 A
GROUP BY A.CARRO

But it returns me error:

  

You can not perform an aggregate function on an expression that contains an aggregate or a subquery.

  • Can not really GROUP BY if it has a subquery to bring column value?
  • Would it be because of the "systemic string" that SQL works, or something like that?
  • What other ways could you do to get the result?
asked by anonymous 08.08.2018 / 16:26

1 answer

4

Perhaps the use of CTE will solve and even make the code more readable. 

-- código #1
with CadaCarro as (
SELECT A.CARRO,
       (SELECT top (1) B.VALOR 
          from tab2 as B 
          where B.DATA <= A.DATA 
          order by B.DATA desc) as VALOR
  from tab1 as A
)
SELECT CARRO, sum(VALOR) as somaVALOR
  from CadaCarro
  group by CARRO;

In the Porto SQL there is an article on modular programming in T-SQL through the use of CTE.

    
08.08.2018 / 17:26
Animation with CSS image Do not display Warning of foreach