SUM From Two Tables

[ptaylor965]

Hi,

In VB.NET

I have Two tables

Table1 = OrderNum, Stockcode, QtyNeeded And Date
Table2 = Stockcode, QtyProduced And Date

I need to get the sum of QtyNeeded and the sum of QtyProduced so that i can see the differance

They are grouped by Stockcode and Date

e.g.

Stockcode | QtyNeeded | QtyProduced | Date
Type1 ---- | ---- 20 ---- | ------ 10 ---- | 23/04/2007
Type2 ---- | ---- 15 ---- | ------ 15 ---- | 23/04/2007

I can get the SUMs indervidually but not together

Table1 ------------------------------ Table2
Stockcode | QtyNeeded | |Stockcode | QtyProduced
Type1 -----| --- 20 ----- | | Type1 --- |------- 5
Type2 -----| --- 10 ----- | | Type1 --- |------- 5
Type2 -----| ---- 5 ----- | | Type2 ---- | -------5
------------------------------| Type2 --- | -------5
------------------------------| Type2 --- | -------5

Any Ideas

Thanks
Peter

[hollystyles]

Select 
    t1.StockCode, 
    sum(t1.QtyNeeded) QtyNeeded, 
    sum(t2.QtyProduced) QtyProduced, 
    max(t2.Date) [Date] 
from
    Table1 t1
    join Table2 t2 on t1.StockCode = t2.StockCode 
group by 
    t1.StockCode 
order by 
    t1.StockCode

Hmm that won;t work cos it's many to many. Have to think a bit more.

[hollystyles]

Try this :

Select 
	t1.StockCode, 
	t1.QtyNeeded, 
	t2.QtyProduced, 
	t2.ProdDate 
from 
	(
	Select 
		StockCode, 
		sum(QtyNeeded) QtyNeeded 
	from
		Table1 
	group by 
		StockCode 
	) t1 join 
	(
	Select 
		StockCode, 
		sum(QtyProduced) QtyProduced, 
		max(ProdDate) ProdDate 
	from
		Table2 
	group by 
		StockCode 
	) t2 on t1.StockCode = t2.StockCode 
order by 
	t1.StockCode

[ptaylor965]

Thanks for the response

I now get this error
Cannot resolve collation conflict for equal to operation.

after checking the table designs i found that the stockcode is set different
Table1 Stockcode = varchar(50)
Table2 Stockcode = varchar(20)

Is there any way rouns this?

P.S. I did not make the tables so i cant change the design

[ptaylor965]

This SQL code worked perfectly.

I had a slight collation problem but fixed that and then the code was perfect

Thanks HollyStyles

Try this :

Select 
    t1.StockCode, 
    t1.QtyNeeded, 
    t2.QtyProduced, 
    t2.ProdDate 
from 
    (
    Select 
        StockCode, 
        sum(QtyNeeded) QtyNeeded 
    from
        Table1 
    group by 
        StockCode 
    ) t1 join 
    (
    Select 
        StockCode, 
        sum(QtyProduced) QtyProduced, 
        max(ProdDate) ProdDate 
    from
        Table2 
    group by 
        StockCode 
    ) t2 on t1.StockCode = t2.StockCode 
order by 
    t1.StockCode

[hollystyles]

I don't think different sizes prevents join on varchar fields. The table columns have different collation settings. There is a way to specify collation in the sql statement, but first you need to look at the tables in design mode in Enterprise manager or whatever and look at the collation property and tell me what they are?

EDIT -> Oh you just posted, well done!