Add values of different selects

Asked

Viewed 1,049 times

1

Good morning To all, I have two select, I am showing next to each other, I need to add the value of one select to the other .. Is that possible? the two select are separate tables, I am displaying a value next to each other, but I need to add these values...Please if anyone knows how to do need help. follows the select code:

Set objConn =  Server.CreateObject("ADODB.Connection")
objConn.Open "DBQ=" & Server.MapPath("banco.mdb") & ";Driver={Microsoft Access Driver (*.mdb)}","username","password"
strQ = "SELECT * FROM Vendas2017 ORDER BY id asc"
Set ObjRs = objConn.Execute(strQ)


Set objConn2 =  Server.CreateObject("ADODB.Connection")
objConn2.Open "DBQ=" & Server.MapPath("banco.mdb") & ";Driver={Microsoft Access Driver (*.mdb)}","username","password"
strQ2 = "SELECT * FROM Vendas2018 ORDER BY id asc"
Set ObjRs2 = objConn2.Execute(strQ2)
  • hello, it’s totally possible but wouldn’t it be more practical to add in the database and already return the value adding with a query? Anyway, to help you show the names of the fields you want to add up, just select * without knowing the table structure doesn’t help much

  • Ola Ricardo, thank you so much for your help, is that what you need? <br/> <br/> strQ = "SELECT * FROM Sales2017 ORDER BY id asc" <br/> strQ2 = "SELECT * FROM Sales2018 ORDER BY id asc" <br/> tableela01: Sales2017 tabela02: Sales2018 <br/> name of column table 01: janvalue name of column 02: janvalor <br/>

  • 2

    you edited the question and lost the text that explained... it would be nice for you to go back to the way it was and just add in the question the names of the columns...

  • one more doubt, each of these select returns how many rows? because if tables Vendas2017 and Vendas2018 return many lines, the SUM in the bank, or even summing in the code, the result can be very large and give error of overflow

  • sorry, I haven’t gotten used to the site yet, exactly what is happening Ricardo gave overflow, are 5000 lines, all I wanted is to put a value next to each other and add

  • I suspected this might happen... :) What kind of field data janvalor?

  • You can use the function CDec to help if your field is not decimal: select sum(CDec(janvalor)) janvalor

Show 2 more comments

2 answers

2

You can add directly into the bank using a third query, that would be so:

select sum(janvalor) as Soma from 
(
     select sum(janvalor) janvalor from Vendas2017 
     union
     select sum(janvalor) janvalor from Vendas2018 
) as Vendas

Or add in the code, but then you need a bow for or foreach to read the records and add.

0

I usually do this with a certain strategy. Easier to show than to explain. Suppose I want to compare sales from different years month to month:

select mes, sum(iif(ano = 2017, valor, 0)) Ex2017, sum(iif(ano = 2018, valor, 0)) Ex2018 from vendas group by mes;

Browser other questions tagged

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