sum aggrigate function problem

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

General SQL Server Forums

New to SQL Server Programming

sum aggrigate function problem

Author

Topic

bndck
Starting Member

7 Posts

Posted - 2011-01-18 : 07:00:00

I am a newbee.. I have 2 tables tbl1 and tbl1 and structures are as follows.

tbl1 - p-code,description,total_pcs,total_mts,recvd_date....
tbl2 - tr_code,dt_deliver,p_code,client_code,pcs,mts...

tbl1
p_code description total_pcs total_mts
AB 111 xxxxxxx 500 5000
AB 222 yyyyyyy 1000 10000

tbl2
tr_code dt_deliver p_code client_code pcs mts
1 1/1/2011 AB 111 C1 10 1000
2 2/1/2011 AN 222 C1 5 500
3 2/1/2011 AB 111 C2 4 450
....
....

i wanna write a query that combine above 2 tables and produce output as follow ( i want to get sum of each against each p_code of tbl2 and subtract from tbl1 opening balance and produce following output. can i use INNER JOIN?..if so how to do it?)..urgent help need

p_code balance_pcs balance_mts .......
AB 111 486 3550
AB 222 995 9500
............

many thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-01-18 : 08:42:52

Try this

select t1.p_code,t1.total_pcs-t2.total_pcs, t1.total_mts-t2.total_mts from tbl1 as t1
inner join
(
select t2.p_code,sum(t2.total_pcs) as total_pcs,SUM(t2.total_mts) as total_mts
from tbl2 as t2
group by t2.p_code
) as t2
on t1.p_code=t2.p_code

Madhivanan

Failing to plan is Planning to fail

bndck
Starting Member

7 Posts

Posted - 2011-01-18 : 11:30:45

quote:
Originally posted by madhivanan

Try this

select t1.p_code,t1.total_pcs-t2.total_pcs, t1.total_mts-t2.total_mts from tbl1 as t1
inner join
(
select t2.p_code,sum(t2.total_pcs) as total_pcs,SUM(t2.total_mts) as total_mts
from tbl2 as t2
group by t2.p_code
) as t2
on t1.p_code=t2.p_code

Madhivanan

Failing to plan is Planning to fail

Thanks Madhivanan

I tried ur piece of code..but ended up with syntax error..
this is my sql statement

SELECT Products.Product_Code,Products.Description,Products.Category,Products.rolls - Sold_Products.sold_Rolls,Products.mts - sold_products.sold_mts FROM Products as tb1
INNER JOIN (SELECT Sold_Products.Product_Code,sum(sold_products.sold_rolls) as tt_rolls,sum(Sold_Products.sold_mts) as tt_mts FROM Sold_Products as tb2 GROUP BY Sold_Products.Product_Code) as t2 ON Products.Product_Code = Sold_Products.Product_Code

plz look at if possible..

thanks again

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-01-20 : 09:46:38

quote:
Originally posted by bndck

quote:
Originally posted by madhivanan

Try this

select t1.p_code,t1.total_pcs-t2.total_pcs, t1.total_mts-t2.total_mts from tbl1 as t1
inner join
(
select t2.p_code,sum(t2.total_pcs) as total_pcs,SUM(t2.total_mts) as total_mts
from tbl2 as t2
group by t2.p_code
) as t2
on t1.p_code=t2.p_code

Madhivanan

Failing to plan is Planning to fail

Thanks Madhivanan

I tried ur piece of code..but ended up with syntax error..
this is my sql statement

SELECT Products.Product_Code,Products.Description,Products.Category,Products.rolls - Sold_Products.sold_Rolls,Products.mts - sold_products.sold_mts FROM Products as tb1
INNER JOIN (SELECT Sold_Products.Product_Code,sum(sold_products.sold_rolls) as tt_rolls,sum(Sold_Products.sold_mts) as tt_mts FROM Sold_Products as tb2 GROUP BY Sold_Products.Product_Code) as t2 ON Products.Product_Code = Sold_Products.Product_Code

plz look at if possible..

thanks again

Is this working correctly?

Madhivanan

Failing to plan is Planning to fail

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-20 : 10:31:21

@bndck: the query you are ended up looks like this!!! and the issues are highlighted with Red

SELECT
Products.Product_Code
,Products.Description
,Products.Category
,Products.rolls - Sold_Products.sold_Rolls -- "-" is a problem here .. and there is no table with the name of sold_Products since it has been aliased now
,Products.mts - sold_products.sold_mts ---- "-" is a problem here .. and there is no table with the name of sold_Products since it has been aliased now
FROM Products as tb1
INNER JOIN (
SELECT
Sold_Products.Product_Code
,sum(Sold_Products.sold_rolls) as tt_rolls
,sum(Sold_Products.sold_mts) as tt_mts
FROM Sold_Products as tb2 You have refered Sold_Products as tb2 then use the same Alias for the above highlighted fields and in group by
GROUP BY Sold_Products.Product_Code
) as t2 ON Products.Product_Code = Sold_Products.Product_Code --Sold_Products this one should be placed with Alias t2

AFter re-organizing the syntax of your query it should seems to be some how like this

SELECT
tb1.Product_Code
,tb1.Description
,tb1.Category
,tb1.rolls
,T2.tt_rolls
,T2.tt_mts
FROM Products as tb1
INNER JOIN (
SELECT
tb2.Product_Code
,sum(tb2.sold_rolls) as tt_rolls
,sum(tb2.sold_mts) as tt_mts
FROM Sold_Products as tb2
GROUP BY tb2.Product_Code
) as t2 ON t2.Product_Code = tb1.Product_Code

bndck
Starting Member

7 Posts

Posted - 2011-01-25 : 14:17:58

quote:
Originally posted by MIK_2008

@bndck: the query you are ended up looks like this!!! and the issues are highlighted with Red

SELECT
Products.Product_Code
,Products.Description
,Products.Category
,Products.rolls - Sold_Products.sold_Rolls -- "-" is a problem here .. and there is no table with the name of sold_Products since it has been aliased now
,Products.mts - sold_products.sold_mts ---- "-" is a problem here .. and there is no table with the name of sold_Products since it has been aliased now
FROM Products as tb1
INNER JOIN (
SELECT
Sold_Products.Product_Code
,sum(Sold_Products.sold_rolls) as tt_rolls
,sum(Sold_Products.sold_mts) as tt_mts
FROM Sold_Products as tb2 You have refered Sold_Products as tb2 then use the same Alias for the above highlighted fields and in group by
GROUP BY Sold_Products.Product_Code
) as t2 ON Products.Product_Code = Sold_Products.Product_Code --Sold_Products this one should be placed with Alias t2

AFter re-organizing the syntax of your query it should seems to be some how like this

SELECT
tb1.Product_Code
,tb1.Description
,tb1.Category
,tb1.rolls
,T2.tt_rolls
,T2.tt_mts
FROM Products as tb1
INNER JOIN (
SELECT
tb2.Product_Code
,sum(tb2.sold_rolls) as tt_rolls
,sum(tb2.sold_mts) as tt_mts
FROM Sold_Products as tb2
GROUP BY tb2.Product_Code
) as t2 ON t2.Product_Code = tb1.Product_Code

Hi MIK_2008

awsome dude..!!!!,,u save my years. solution is right on the money. good luck..best wishes..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources