November 21, 2019 at 7:05 am
Hello,
I have the following queries that I want to group together in one optimized query. I would like to compare the difference between movements and balances with movements and balances and add the difference in another column
select nom_banque, code_banque, debit_banque - credit_banque as solde
from ma_banque
where type = 'BANCAIRE'
and origine = 'SOLDE'
select nom_banque, code_banque, sum(debit_banque) - sum(credit_banque) as solde
from ma_banque
where type = 'BANCAIRE'
and origine = 'MOUVEMENT'
group by nom_banque, code_banque
select nom_banque, code_banque, debit_banque - credit_banque as solde
from ma_banque
where type = 'COMPTABLE'
and origine = 'SOLDE'
select nom_banque, code_banque, sum(debit_banque) - sum(credit_banque) as solde
from ma_banque
where type = 'COMPTABLE'
and origine = 'MOUVEMENT'
November 22, 2019 at 7:10 am
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
November 22, 2019 at 2:48 pm
Do you have an example of the output you are expecting to generate? It feels like one or two CTEs which are then joined may be the way, but it is difficult without a better idea of what you are looking for.
November 22, 2019 at 7:49 pm
You may be able to do this using sub queries but that won't really help you with one single query. CTE is probably the way to go like SQLian mentioned.
November 24, 2019 at 2:03 am
Hello,
I have the following queries that I want to group together in one optimized query. I would like to compare the difference between movements and balances with movements and balances and add the difference in another column
select nom_banque, code_banque, debit_banque - credit_banque as solde
from ma_banque
where type = 'BANCAIRE'
and origine = 'SOLDE'
select nom_banque, code_banque, sum(debit_banque) - sum(credit_banque) as solde
from ma_banque
where type = 'BANCAIRE'
and origine = 'MOUVEMENT'
group by nom_banque, code_banque
select nom_banque, code_banque, debit_banque - credit_banque as solde
from ma_banque
where type = 'COMPTABLE'
and origine = 'SOLDE'
select nom_banque, code_banque, sum(debit_banque) - sum(credit_banque) as solde
from ma_banque
where type = 'COMPTABLE'
and origine = 'MOUVEMENT'
I don't think your SQL is correct.
select nom_banque, code_banque, sum(debit_banque) - sum(credit_banque) as solde
from ma_banque
where type = 'COMPTABLE'
and origine = 'MOUVEMENT'
will need a GROUP BY to compile.
Did you actually want a SUM and GROUP BY in each query? e.g:
select nom_banque, code_banque, sum(debit_banque) - sum(credit_banque) as solde
from ma_banque
where type = 'xxx'
and origine = 'yyy'
group by nom_banque, code_banque
November 25, 2019 at 8:29 pm
The query below will produce the combined results of your four queries. However, I think it would be more meaningful if you also included the TYPE and the ORIGINE columns in both the SELECT list and the GROUP BY clause.
select nom_banque, code_banque, debit_banque - credit_banque as solde
from ma_banque
where type in ('COMPTABLE','BANCAIRE')
and origine = 'SOLDE'
UNION ALL
select nom_banque, code_banque, sum(debit_banque) - sum(credit_banque) as solde
from ma_banque
where type in ('COMPTABLE','BANCAIRE')and origine = 'MOUVEMENT'
group by nom_banque, code_bank
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply