CODEWITHSUNDEEP
sqlsql-server
hamza.soufiane
hamza.soufiane

Reputation: 15

HOW TO COMBINE 2 QUERIES

I have 2 queries : First will show stock & second for sales.

--STOCK DEPOT

SELECT GQ_ARTICLE AS 'CODE ARTICLE', 

SUM (GQ_PHYSIQUE)

FROM DISPO

GROUP BY GQ_ARTICLE

--SALES IN 6 MONTHS

--UNION ALL


SELECT GL_CODEARTICLE AS 'CODE ARTICLE',
SUM(GL_QTEFACT)AS 'SOLD QUANTITY'

FROM GCLIGNEARTDIM 

WHERE

DATEADD(M, 6, GP_DATEPIECE) > getdate()

GROUP BY GL_CODEARTICLE

I tried union ALL to combine between the 2 queries but it take a long time to be executed. even i take just 1 day.

Any solution please ?

I want a result like this :

GQ_ARTICLE   SUM (GQ_PHYSIQUE)   SUM (GQ_PHYSIQUE)
xxxx         24                  50

Upvotes: 0

Views: 82

Answers (3)

john McTighe
john McTighe

Reputation: 1181

if the columns GQ_ARTICLE & GL_CODEARTICLE refer to the same thing then you can simply join the tables and use a case statement. eg

SELECT GQ_ARTICLE AS 'CODE ARTICLE', 
SUM (GQ_PHYSIQUE),
SUM(CASE WHEN DATEADD(M, 6, GP_DATEPIECE) > getdate() THEN GL_QTEFACT ELSE 0 END)

FROM DISPO
JOIN GCLIGNEARTDIM
ON GQ_ARTICLE=GL_CODEARTICLE
GROUP BY GQ_ARTICLE

Edit - you might need a LEFT/RIGHT/FULL OUTER Join here depending on your data...Easy to change though...

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269463

You seem to want a full join not union all because you want the results on a single row:

SELECT COALESCE(l.CODE_ARTICLE, d.CODE_ARTICLE) as CODE_ARTICLE,
       d.SUM_PHYSIQUE, l.SOLD_QUANTITY       
FROM (SELECT GQ_ARTICLE AS CODE_ARTICLE, SUM(GQ_PHYSIQUE) as SUM_PHYSIQUE
      FROM DISPO
      GROUP BY GQ_ARTICLE
     ) d FULL JOIN
     (SELECT GL_CODEARTICLE AS CODE_ARTICLE, SUM(GL_QTEFACT) AS SOLD_QUANTITY
      FROM GCLIGNEARTDIM 
      WHERE GP_DATEPIECE > DATEADD(month, -6, getdate())
      GROUP BY GL_CODEARTICLE
     ) l
     ON l.CODE_ARTICLE = d.CODE_ARTICLE;

Note the change to the WHERE condition in the second query. This will allow the query to take advantage of indexes and partitions, if appropriate.

Upvotes: 0

Ayush Singh
Ayush Singh

Reputation: 53

A union between two completely unrelated tables:

select
    FirstName as Column1,
    LastName as Column2,
    email as Column3,
    null as Column4
from
   stock
union
select
    ProductName as Column1,
    QuantityPerUnit as Column2,
    null as Column3,
    UnitsInStock as Column4
from
    product

Upvotes: 2

Related Questions