Join query one field in one table and sum same field from another

[jj22171]

I have the following tables:
Table1
keyFld fldGroup fldAmount fldA fldB
key1 A 10
key1 A 10
key1 B 10
key1 B 10
key1 B 10
key1 C 10
key1 C 10
Table2
keyFld fldGroup fldAmount fldD fldE
key1 A 20
key1 B 30
key1 C 20
Now, I want to compare the sum of Table1 GROUP BY fldGroup with Table2 and should be able to pickup fields fldA to fldE from both tables..

I would greatly appreciate any help. thanks

 

[bborissov]

And the desired result from that data is?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[AlexCuse]

I really have no idea what you are looking for, but I think its' something along these lines? (notice that I omitted data for Group C, to see if this is results you want).

Do you only want to show rows where there is NOT a match between 'total' in table2 and sum from table1 or something?

[COLOR=blue]declare[/color] @table1 [COLOR=blue]table[/color] (keyFld [COLOR=blue]varchar[/color](10), fldGroup [COLOR=blue]char[/color](1), fldAmount [COLOR=blue]int[/color])
[COLOR=blue]insert[/color] @table1
[COLOR=blue]select[/color] [COLOR=red]'key1'[/color], [COLOR=red]'A'[/color], 10 
union all [COLOR=blue]select[/color] [COLOR=red]'key1'[/color], [COLOR=red]'A'[/color], 10
union all [COLOR=blue]select[/color] [COLOR=red]'key1'[/color], [COLOR=red]'B'[/color], 10
union all [COLOR=blue]select[/color] [COLOR=red]'key1'[/color], [COLOR=red]'B'[/color], 10
union all [COLOR=blue]select[/color] [COLOR=red]'key1'[/color], [COLOR=red]'B'[/color], 10

[COLOR=blue]declare[/color] @table2 [COLOR=blue]table[/color] (keyFld [COLOR=blue]varchar[/color](10), fldGroup [COLOR=blue]char[/color](1), fldAmount [COLOR=blue]int[/color])
[COLOR=blue]insert[/color] @table2
[COLOR=blue]select[/color] [COLOR=red]'key1'[/color], [COLOR=red]'A'[/color], 20
union all [COLOR=blue]select[/color] [COLOR=red]'key1'[/color], [COLOR=red]'B'[/color], 30
union all [COLOR=blue]select[/color] [COLOR=red]'key1'[/color],  [COLOR=red]'C'[/color], 20


[COLOR=blue]select[/color] [COLOR=#FF00FF]coalesce[/color](t1.keyFld, t2.keyFld)
, [COLOR=#FF00FF]coalesce[/color](t1.fldGroup, t2.fldGroup) [COLOR=blue]as[/color] fldGroup
, sum(t1.fldAmount) [COLOR=blue]as[/color] SumAmt
, t2.fldAmount [COLOR=blue]as[/color] TotalAmt
[COLOR=blue]from[/color] @Table1 t1 [COLOR=blue]FULL[/color] [COLOR=blue]OUTER[/color] [COLOR=blue]JOIN[/color] @Table2 t2
[COLOR=blue]on[/color] t1.keyFld = t2.keyFld
and t1.fldGroup = t2.fldGroup
[COLOR=blue]group[/color] [COLOR=blue]by[/color] [COLOR=#FF00FF]coalesce[/color](t1.keyFld, t2.keyFld), [COLOR=#FF00FF]coalesce[/color](t1.fldGroup, t2.fldGroup), t2.fldAmount

Adding your other columns should be a piece of cake.

Hope this helps,

ALex

Ignorance of certain subjects is a great part of wisdom

 

[jj22171]

The result needed is:
fldGroup sumTable1Amt Table2Amt fldA fldB fldD fldE
A 30 30 W X Y Z
B 20 20 W X Y Z
C 20 20 W X Y Z
D 10 0 W X Y Z

I added D with no matching record in Table2. Sorry for missing this info. Thanks

 

[AlexCuse]

jj22171 -

That is a pretty easy modification from the query I posted. Just one more COALESCE^* and some additional column names.

Anyway try this:

[COLOR=blue]select[/color] [COLOR=#FF00FF]coalesce[/color](t1.fldGroup, t2.fldGroup) [COLOR=blue]as[/color] fldGroup
, sum(t1.fldAmount) [COLOR=blue]as[/color] SumAmt
, [COLOR=#FF00FF]coalesce[/color](t2.fldAmount, 0) [COLOR=blue]as[/color] TotalAmt
, t1.fldA
, t1.fldB
, t2.fldD
, t2.fldE
[COLOR=blue]from[/color] Table1 t1 [COLOR=blue]FULL[/color] [COLOR=blue]OUTER[/color] [COLOR=blue]JOIN[/color] Table2 t2
[COLOR=blue]on[/color] t1.keyFld = t2.keyFld
and t1.fldGroup = t2.fldGroup
[COLOR=blue]group[/color] [COLOR=blue]by[/color] [COLOR=#FF00FF]coalesce[/color](t1.keyFld, t2.keyFld), [COLOR=#FF00FF]coalesce[/color](t1.fldGroup, t2.fldGroup), t2.fldAmount

^* == Read about this in books online (SQL Server Help)

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[jj22171]

Thanks Alex. The Sum works great. Unfortunately, it's giving me "invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause" error message for each additional fields not included GROUP BY.

 

[AlexCuse]

You can remove your keyFld expression and add these to the group by:

, t1.fldA
, t1.fldB
, t2.fldD
, t2.fldE

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[jj22171]

I excluded those fields from the GROUP BY thinking the sum would be by fldGroup, fldA, fldB, etc. Well, wrong assumption.

It's now working perfectly. Thanks a lot. Alex.

 

[AlexCuse]

No problem. I am glad you have it working

Ignorance of certain subjects is a great part of wisdom