CODEWITHSUNDEEP
sqlsql-server
Phra Tankian
Phra Tankian

Reputation: 68

Join Query Results

i have a problem, i have a table were i keep the incoming and other one were i keep the outgoing records, the thing that i need to do is to group these records by partNumber and have a single output.

InTable

PartNo Qty Date
A       1   1/1/13
A       5   1/1/13
B       2   1/1/13

OutTable

PartNo Qty Date
A       2  1/1/13
B       1  1/1/13
C       3  1/1/13


Result Needed

Date 1/1/13

PartNo In Out Total
A      6   2    4
B      2   1    1
C      0   3    -3

I have tried something like this but it results on the totals because of the sum(qty), but it does not work any other way.

select a.PartNo, sum(b.inQty) as inQty,sum(c.outQty) as outQty, sum(b.inQty)-sum(c.outQty) as total  from 
  (Select PartNo FROM InTable
  where date= '01-01-2013'
  group by PartNo
  union
  Select PartNo  FROM OutTable
  where date= '01-01-2013'
  group by PartNo) A
  cross join
  (  
  SELECT PartNo,SUM(Qty) inQty FROM InTable
  where date= '01-01-2013'
  group by PartNo
  )B
  cross join
  ( 
  SELECT PartNo,SUM(Qty) outQty FROM OutTable
  where date= '01-01-2013'
  group by PartNo
  )c
  group by a.PartNo

There i tried to join three queries, each query individually results in something helpfull, but the problem is when i try to join them, the query will result in something like

PartNo inQty outQty total 
A       8       6     2
B       8       6     2
C       8       6     2

Any sugestions?, thanks.

Upvotes: 3

Views: 97

Answers (7)

Kaf
Kaf

Reputation: 33809

Try this using UNION ALL instead of FULL OUTER JOIN(Fiddle demo)

;with cte as (
    select partNo, Qty inQty, 0 outQty, mydate
    from inTable
    union all
    select partNo, 0 inQty, qty outQty, mydate
    from outTable
)
select partNo, sum(inQty) inQty,
               sum(outQty) outQty,
               sum(inQty) - sum(outQty) total
from cte
where mydate = '01/01/2013'
group by partNo
order by partNo

Results

| PARTNO | INQTY | OUTQTY | TOTAL |
---------|-------|--------|-------|
|      A |     6 |      2 |     4 |
|      B |     2 |      1 |     1 |
|      C |     0 |      3 |    -3 |

Upvotes: 2

danisius
danisius

Reputation: 597

Idid`t saw derivered table so here is my solution ported from Firebired

select t.id as [PartNo],
       sum(T.In) as [IN],
       sum(t.Out) as [Out],
       sum(T.inc)-sum(t.decc) as "Total" 
from (
     select p.Partno as [id] ,p.id as [In],0    as [Out],p.Date as dt from PARTSIN p
     union
     select o.Partno as [id], 0    as [In],o.id as [Out,o].Date as dt from PARTSOUT o
) as t
where t.dt='1/1/2013'
group by t.id;

Upvotes: 0

Josh Jay
Josh Jay

Reputation: 1250

Use a Full Join

SELECT
  Coalesce(I.PartNo,O.PartNo) AS PartNo,
  IsNull(I.Qty,0) AS [In],
  IsNull(O.Qty,0) AS [Out],
  IsNull(I.Qty,0) - IsNull(O.Qty,0) AS [Total]
FROM
  (SELECT PartNo, Sum(Qty) AS Qty FROM InTable WHERE Date = '1/1/2013' GROUP BY PartNo) I
  FULL JOIN
  (SELECT PartNo, Sum(Qty) AS Qty FROM OutTable WHERE Date = '1/1/2013' GROUP BY PartNo) O ON I.PartNo = O.PartNo;

Sql Fiddle Example

Upvotes: 1

Sparky
Sparky

Reputation: 15075

Try this:

select isNull(a.PartNo,b.PartNo) as PartNo,
       IsNull(sum(a.qty),0) as InAmt,
       IsNull(sum(b.qty),0) as OutAmt,
       IsNull(sum(a.qty),0)-IsNull(sum(b.qty),0) as Diff
from Intable a
full join OutTable b on a.partno=b.partNo
group by isNull(a.PartNo,b.PartNo)

SQLFiddle: http://www.sqlfiddle.com/#!3/5d06d/8

Upvotes: 0

JayGee
JayGee

Reputation: 342

You need to use a FULL JOIN instead of a CROSS JOIN. You're joining A to all the rows returned from B and C.

select a.PartNo, sum(b.inQty) as inQty,sum(c.outQty) as outQty, sum(b.inQty)-sum(c.outQty) as total  from 
  (Select PartNo FROM InTable
  where date= '01-01-2013'
  group by PartNo
  union
  Select PartNo  FROM OutTable
  where date= '01-01-2013'
  group by PartNo) A
  FULL JOIN
  (  
  SELECT PartNo,SUM(Qty) inQty FROM InTable
  where date= '01-01-2013'
  group by PartNo
  ) B ON b.partno = a.partno
  FULL JOIN
  ( 
  SELECT PartNo,SUM(Qty) outQty FROM OutTable
  where date= '01-01-2013'
  group by PartNo
  ) c ON c.partno = a.partno
  group by a.PartNo

Editted Changed from a LEFT JOIN to a FULL JOIN because you want to see a row even if it's only in the intable or only in the outtable.

Upvotes: 0

dnoeth
dnoeth

Reputation: 60472

Use a Full Outer Join on two Derived Tables:

SELECT 
    COALESCE(inTab.PartNo, outTab.PartNo) AS PartNo,
    COALESCE(inQty, 0),
    COALESCE(outQty, 0),
    COALESCE(inQty, 0) -  COALESCE(outQty, 0) AS total
FROM 
 (
   SELECT PartNo, SUM(Qty) AS inQty 
   FROM InTable
   WHERE DATE= '01-01-2013'
   GROUP BY PartNo
 ) InTab
FULL JOIN
 (
   SELECT PartNo, SUM(Qty) AS outQty 
   FROM OutTable
   WHERE DATE= '01-01-2013'
   GROUP BY PartNo
 ) OutTab
ON inTab.Partno = outTab.PartNo

Upvotes: 2

Sonam
Sonam

Reputation: 3466

with part_cte(partno,date)as
(select partno,date from Intable
Union
Select partno,date from OutTable)

,intable_cte(partno,Qty,date)AS
(select Partno,sum(Qty)qty,Date from Intable Group By Partno,Date)

, outtable_cte(partno,Qty,date)AS
(select Partno,sum(Qty)qty,Date from outtable Group By Partno,Date)

select PC.partno,ISNULL(IC.QTY,0),ISNULL(OC.qty,0),PC.Date
From part_cte PC LEFT Join intable_cte IC on PC.partno=IC.partno and PC.date=IC.Date
LEFT Join outtable_cte OC on PC.partno=OC.partno and PC.date=OC.Date

Upvotes: 0

Related Questions