need help on a request

Question

Post reply

need help on a request

abdalah.mehdoini

Ten Centuries

Points: 1015

November 21, 2019 at 7:05 am

#3699469

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'

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80325 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

SQLian SSC-Addicted Points: 413 More actions · Answer 2

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.

blakemcneill SSC Enthusiast Points: 162 More actions · Answer 3

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.

Jonathan AC Roberts SSCoach Points: 18361 More actions · Answer 4

abdalah.mehdoini wrote:

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

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 5

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

This reply was modified 5 years ago by The Dixie Flatline.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills