sum of two queries

Question

Post reply

sum of two queries

136romy

SSChasing Mays

Points: 634
More actions
March 18, 2014 at 4:30 am

#284744

hi community here I have two queries that I want to sum my stock but I do not know how. if you please help me:
1)request for the sum of inputs
select ARTICLE.CODART,ARTICLE.DESIGART,ARTICLE.SEUILMINI,mouvement.DATMVT,TYPE_MOUVEMENT.LIBTYPMVT,
SITE.LIBSITE,sous_site.LIBSOUSIT,SUM(FAIRE.QTEMVT) as qte_entree
from FAIRE faire, mouvement mouvement, TYPE_MOUVEMENT TYPE_MOUVEMENT, ARTICLE ARTICLE,
SITE site,SOUS_SITE sous_site
where faire.CODMVT=mouvement.CODMVT and TYPE_MOUVEMENT.CODTYPMVT= mouvement.CODTYPMVT and site.CODSITE=sous_site.CODSITE and sous_site.CODSOUSIT= mouvement.CODSOUSIT
and faire.CODART=ARTICLE.CODART and TYPE_MOUVEMENT.LIBTYPMVT like 'entr%'
group by ARTICLE.CODART,ARTICLE.DESIGART,
ARTICLE.SEUILMINI,mouvement.DATMVT,TYPE_MOUVEMENT.LIBTYPMVT,SITE.LIBSITE,sous_site.LIBSOUSIT
2)request for the sum of outputs
select ARTICLE.CODART,ARTICLE.DESIGART,ARTICLE.SEUILMINI,mouvement.DATMVT,TYPE_MOUVEMENT.LIBTYPMVT,
SITE.LIBSITE,sous_site.LIBSOUSIT,SUM(FAIRE.QTEMVT) as qte_sortie
from FAIRE faire, mouvement mouvement, TYPE_MOUVEMENT TYPE_MOUVEMENT, ARTICLE ARTICLE,
SITE site,SOUS_SITE sous_site
where faire.CODMVT=mouvement.CODMVT and TYPE_MOUVEMENT.CODTYPMVT= mouvement.CODTYPMVT and site.CODSITE=sous_site.CODSITE and sous_site.CODSOUSIT= mouvement.CODSOUSIT
and faire.CODART=ARTICLE.CODART and TYPE_MOUVEMENT.LIBTYPMVT like'sorti%'
group by ARTICLE.CODART,ARTICLE.DESIGART,
ARTICLE.SEUILMINI,mouvement.DATMVT,TYPE_MOUVEMENT.LIBTYPMVT,SITE.LIBSITE,sous_site.LIBSOUSIT

Viewing 10 posts - 1 through 9 (of 9 total)

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

HanShi SSC-Dedicated Points: 33507 More actions · Answer 1

Create the same query and alter the WHERE clause part on TYPE_MOUVEMENT.LIBTYPMVT to:

... and (TYPE_MOUVEMENT.LIBTYPMVT like 'sorti%' OR TYPE_MOUVEMENT.LIBTYPMVT like 'entr%') ...

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

136romy (3/18/2014)
hi community here I have two queries that I want to sum my stock but I do not know how....

Use table aliases to eliminate unnecessary noise from your queries, and proper joins for clarity:

SELECT

a.CODART,

a.DESIGART,

a.SEUILMINI,

m.DATMVT,

tm.LIBTYPMVT,

s.LIBSITE,

ss.LIBSOUSIT,

SUM(CASE WHEN tm.LIBTYPMVT LIKE 'entr%' THEN f.QTEMVT ELSE 0 END) as qte_entree,

SUM(CASE WHEN tm.LIBTYPMVT LIKE 'sorti%' THEN f.QTEMVT ELSE 0 END) as qte_sortie

FROM FAIRE f

INNER JOIN mouvement m ON f.CODMVT = m.CODMVT

INNER JOIN TYPE_MOUVEMENT tm ON tm.CODTYPMVT = m.CODTYPMVT

INNER JOIN ARTICLE a ON f.CODART = a.CODART

INNER JOIN SOUS_SITE ss ON ss.CODSOUSIT = m.CODSOUSIT

INNER JOIN SITE s ON s.CODSITE = ss.CODSITE

WHERE tm.LIBTYPMVT LIKE 'entr%'

OR tm.LIBTYPMVT LIKE 'sorti%'

GROUP BY

a.CODART,

a.DESIGART,

a.SEUILMINI,

m.DATMVT,

tm.LIBTYPMVT,

s.LIBSITE,

ss.LIBSOUSIT

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

136romy SSChasing Mays Points: 634 More actions · Answer 3

HanShi when I apply your proposal query gives me the results with lots of numbers, but not my stock

HanShi SSC-Dedicated Points: 33507 More actions · Answer 4

136romy (3/18/2014)
HanShi when I apply your proposal query gives me the results with lots of numbers, but not my stock

Take the advise of ChrisM to write your query in a more readable way. You could extend his solution by adding

"SUM(f.QTEMVT) as qte_total" to the SELECT part. Then you get for each row the sum of "entr", the sum of "sorti" and the sum of both.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

136romy SSChasing Mays Points: 634 More actions · Answer 5

hi chrism@Work thanks for your reply but when I apply your solution, here in attachment is the result I get:

but I would like to have a column that displays EtatStock the result of the difference between qte_entree and qte_sortie according to DESIGART column. eg "Amoxicillin 250 mg sirop" I wish I had 80 - (5 +10) = 65.

65 here is EtatStock article "Amoxicillin 250 mg sirop"

HanShi SSC-Dedicated Points: 33507 More actions · Answer 6

The value in column "tm.LIBTYPMVT" differs, so this will result in a single row for each value. If you remove column "tm.LIBTYPMVT" from the SELECT list, the results will be GROUPED to only one single row for each DESIGART.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

HanShi (3/18/2014)
The value in column "tm.LIBTYPMVT" differs, so this will result in a single row for each value. If you remove column "tm.LIBTYPMVT" from the SELECT list, the results will be GROUPED to only one single row for each DESIGART.

DATMVT also.

You could an aggregate function to retain one of the values of LIBTYPMVT, DATMVT

e.g. MIN(LIBTYPMVT)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

HanShi SSC-Dedicated Points: 33507 More actions · Answer 8

ChrisM@Work (3/18/2014)

DATMVT also.
You could an aggregate function to retain one of the values of LIBTYPMVT, DATMVT
e.g. MIN(LIBTYPMVT)

Good call, I missed that one 😉

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

136romy SSChasing Mays Points: 634 More actions · Answer 9

I have grouped the results according to max (m.DATMVT) but I still do not know how to do subtraction qte_entree qte_sortie and for all according to my EtatStock approach chrism @ Work decrementing the qte_entree column. attachment in the result of my modification

Reminder: qte_entree = input quantity and

qte_sortie = output quantity