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 2008 Forums
 Transact-SQL (2008)
 need help to sum

Author  Topic 

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2012-04-19 : 00:18:38
Let's say my table and data as following,

declare @tH table (idx int, collector varchar(10), receiptNo varchar(16),
sysNo varchar(16), payerID int);
insert into @tH values(1, '1925', '018567', '00001', 10);
insert into @tH values(2, '1925', '018568', '00002', 9);
insert into @tH values(3, '1925', '018569', '00003', 13);
/*
idx is a primary key
receiptNo is a unique key
sysNo is a unique key
relationship @tH to @tD is 1 to many
*/

declare @tD table (tH_idx int, paymentType varchar(10), amount decimal(10,2));
insert into @tD values(1, '80090', 320.10);
insert into @tD values(1, '80091', 20.50);
insert into @tD values(1, '80097', 400.10);
insert into @tD values(2, '80090', 400);
insert into @tD values(2, '80091', 95.60);
insert into @tD values(3, '80092', 70.40);
insert into @tD values(3, '80094', 300.00);
insert into @tD values(3, '80099', 400.10);
/*
@tD(tH_idx) is a foreign key to @tH(idx)
*/


my expected result as following,

idx | collector | receiptNo | sysNo | payerID | totalAmount
-----------------------------------------------------------------------------
1 1925 018567 00001 10 740.70
2 1925 018568 00002 9 495.60
3 1925 018569 00003 13 770.50


need help to built SQL statement

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-19 : 01:15:27
can we see what you tried yet? A simple GROUP BY over id and applying sum will give you result

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2012-04-19 : 01:28:14
Sir,

I've
select t1.idx, t1.collector, t1.sysNo, t1.receiptNo, t1.payerID, t2.paymentType,
SUM(t2.amount) from @tH t1 inner join @tD t2
on t1.idx=t2.tH_idx
group by t1.idx


Error return,
Column '@tH.collector' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-19 : 01:32:35
it should be


SELECT t.*,t1.totalAmount
FROM @tH t
INNER JOIN (SELECT tH_idx,SUM(amount) AS totalAmount
FROM @tD
GROUP BY tH_idx
)t1
ON t1.tH_idx = t.idx


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -