GROUP BY in query with column by subquery

Asked

Viewed 2,755 times

4

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 concerning that DATA or the "most recent".

I tried to make, um GROUP BY for CARRO, various 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 returns me error:

It is not possible to perform an aggregation function in an expression that contains an aggregate or a sub-consumption.

  • You really can’t do the GROUP BY if you have a subquery to bring value from the column?
  • It would be because of the "systemic sequence" that SQL works, or something like?
  • What other ways could you do to get the result?
sql sql-server
  • 3

    "no way to do GROUP BY if you have a subquery" unfortunately no. You would have to put everything in a temporary table for example in order to group. Think the group by and the aggregation functions work "together" to produce the query result, but a subquery harms that

  • Why GROUP BY? The table tab1 has more than one line for the same value of CARRO? // There is no correlation between tab1 and tab2 by the column CARRO?

  • @Ricardopunctual Can I then create a view right!?... would be easier than temporary table

  • @Josédiz yes, has several records, and no, unrelated. The relationship between them is still much worse than in this example !

  • The solution would be to make a JOIN with a SELECT where is the sum of the value grouped by CARRO, but there had to be a relationship between the tab1 and the tab2.

  • @Joãomartins Oh yes... but unfortunately it’s unrelated.

  • 1

    And should I not? Otherwise how do you know which records in the table tab1 relate to those of tab2?

  • @Joãomartins No. There is much more Jooin and select in the query. The ratio is minimal near the large period. Besides that there is no way, as it is a system that I can create tables in client layer only. 100% discarded.

Show 3 more comments

1 answer

5


Maybe 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 SQL Port there is an article on modular programming in T-SQL through the use of CTE.

  • Dude, you killed him!

Browser other questions tagged sql sql-server

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