Problem with sum and Group By - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Problem with sum and Group By

Author

Topic

bettyatolive
Starting Member

21 Posts

Posted - 2006-06-13 : 07:45:09

When i try sum() for one table it is giving me correct values , whereas when i use the sum() function for two different tables it is giving me the double values...

Here is the query i'm using:

SELECT sh.SH_SKID_FK, sum( sh.SH_Received ) , sum( so.SO_Sold ) 
FROM Tbl_HO_StockHistory sh, Tbl_HO_StockOutwards so
WHERE so.SO_SKID_FK = sh.SH_SKID_FK
GROUP BY sh.SH_SKID_FK

the table structure is as follows:
Tbl_HO_StockHistory ( SH_SHID_FK,SH_Received)
Tbl_HO_StockOutwards(SO_SKID_FK,SH_Sold)

when i use the above query the output i'm getting is:
SH_SKID_FK sum( sh.SH_Received ) sum( so.SO_Sold )
87 50 21
88 20 8
89 30 14
90 30 14
91 44 14
92 50 34
93 40 20
94 60 30

While the output that i should get is:
SH_SKID_FK sum( sh.SH_Received ) sum( so.SO_Sold )
87 25 7
88 10 3
89 15 7
90 15 7
91 22 7
92 25 17
93 20 10
94 30 15

Can somebody tell me .... y it is like this....????

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-06-13 : 07:59:05

Can U Just post the data for both tables

Thanks
KK

bettyatolive
Starting Member

21 Posts

Posted - 2006-06-13 : 08:07:58

Data for SH_Received table is:
Tbl_HO_StockHistory ( SH_SHID_FK,SH_Received)
87 25
88 10
89 15
90 15
91 22
92 25
93 20
94 30

Data for StockOutward table is:
Tbl_HO_StockOutwards(SO_SKID_FK,SH_Sold)
87 7
88 3
89 7
90 7
91 7
92 17
93 10
94 10

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-13 : 08:14:36

Try to rewrite query as

SELECT		z.SKID_FK,
		(SELECT SUM(sh.SH_Received) FROM @Tbl_HO_StockHistory sh WHERE sh.SH_SKID_FK = z.SKID_FK),
		(SELECT SUM(so.SO_Sold) FROM @Tbl_HO_StockOutwards so WHERE so.SO_SKID_FK = z.SKID_FK)
FROM		(
			SELECT	SO_SKID_FK SKID_FK
			FROM	@Tbl_HO_StockOutwards
			UNION
			SELECT	SH_SKID_FK
			FROM	@Tbl_HO_StockHistory
		) z
GROUP BY	z.SKID_FK
ORDER BY	z.SKID_FK

Why? That's because your tables allows many-to-many-relationship.

Peter Larsson
Helsingborg, Sweden

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-06-13 : 08:29:44

i think his query is correct

Thanks
KK

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-13 : 08:32:23

Here is another example that does not use that many subqueries and uses less resources!

SELECT		z.SKID_FK,
		SUM(z.Received) AS Received,
		SUM(z.Sold) AS Sold
FROM		(
			SELECT		SH_SKID_FK AS SKID_FK,
					SUM(SH_Received) AS Received,
					0 AS Sold
			FROM		@Tbl_HO_StockHistory
			GROUP BY	SH_SKID_FK

			UNION ALL

			SELECT		SO_SKID_FK,
					0,
					SUM(SO_Sold)
			FROM		@Tbl_HO_StockOutwards
			GROUP BY	SO_SKID_FK
		) z
GROUP BY	SKID_FK
ORDER BY	SKID_FK

Peter Larsson
Helsingborg, Sweden

bettyatolive
Starting Member

21 Posts

Posted - 2006-06-13 : 08:42:53

Hey i actually have another table Tbl_HO_Stock(ID,name,SK_RCSKID_FK)
this table is related as follows:
SK_RCSKID_FK=SH_SKID_FK
SK_RCSKID_FK=SO_SKID_FK

And here i'm giving you the data of this table also
Tbl_HO_Stock(ID,name,SK_RCSKID_FK)
1,stk1,87
2,stk2,88
3,stk3,89
4,stk4,90
5,stk5,91
6,stk6,92
7,stk7,93
8,stk8,94

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-13 : 09:00:48

Are you sure? There are no more surprises?

SELECT		Tbl_HO_Stock.name AS StockName,
		SUM(Tbl_HO_StockHistory.SH_Received) AS Received,
		SUM(Tbl_HO_StockOutwards.SO_Sold) AS Sold
FROM		Tbl_HO_Stock
LEFT JOIN	Tbl_HO_StockHistory ON Tbl_HO_StockHistory.SH_SKID_FK = Tbl_HO_Stock.SK_RCSKID_FK
LEFT JOIN	Tbl_HO_StockOutwards ON Tbl_HO_StockOutwards.SO_SKID_FK = Tbl_HO_Stock.SK_RCSKID_FK
GROUP BY	Tbl_HO_Stock.name
ORDER BY	Tbl_HO_Stock.name

Peter Larsson
Helsingborg, Sweden

bettyatolive
Starting Member

21 Posts

Posted - 2006-06-13 : 10:38:28

Hey it is still giving me the same result... i.e. all the values doubled...

bettyatolive
Starting Member

21 Posts

Posted - 2006-06-13 : 10:44:13

Can you give me some other way of doing tht ... Plz...

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-06-13 : 13:50:15

If you have two separate transaction tables, and you want to relate them, you must first summarize them seperately by a common PK and *then* you can join them together. You cannot join and sum up two transaction tables in 1 big SELECT.

Pesa was close, but each LEFT OUTER JOIN should be to a derived table that sum's up the value being returned and groups by whatever column relates to the Stock table.

By the way -- absolutely, positively, the *worst* table and column names I have ever seen in my life. Uggh.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-13 : 16:00:55

Last chance...

SELECT		a.name,
		ISNULL(r.Received, 0) AS Received,
		ISNULL(s.Sold, 0) AS Sold
FROM		(
			SELECT DISTINCT	name,
					SK_RCSKID_FK
			FROM		Tbl_HO_Stock
		) a
LEFT JOIN	(
			SELECT		SH_SKID_FK AS SKID_FK,
					SUM(SH_Received) AS Received
			FROM		@Tbl_HO_StockHistory
			GROUP BY	SH_SKID_FK
		) r ON r.SH_SKID_FK = a.SK_RCSKID_FK
LEFT JOIN	(
			SELECT		SO_SKID_FK,
					SUM(SO_Sold) AS Sold
			FROM		@Tbl_HO_StockOutwards
			GROUP BY	SO_SKID_FK
		) s ON s.SO_SKID_FK = a.SK_RCSKID_FK
ORDER BY	a.name