i having trouble counting totalamount incrementing many more number of policies there iterating through each row.
for example consider following code:
select customer.custno, customer.entereddate 'date entered', count(basicpolinfo.polid) 'number of policies', sum( count(basicpolinfo.polid)) over() totalamount customer inner join basicpolinfo on customer.custid = basicpolinfo.custid basicpolinfo.polid not null , customer.firstname not null , customer.entereddate > '1/1/79' group customer.custno, customer.firstname, customer.lastname, customer.entereddate order customer.entereddate asc what see totalamount column added number of policies iterating through each , every customer.
ex:
21 -- date -- 6 -- 6 24 -- date -- 13 -- 19 25 -- date -- 23 -- 32 29 -- date -- 16 -- 48 i care less order of custno, rather more concerned if total policies 159703? there more 1000 rows in sql.
please me how able sum each row preceding total sum!
in sql server 2012 forward can use rows in analytic/window function running aggregate:
select customer.custno , customer.entereddate 'date entered' , count(basicpolinfo.polid) 'number of policies' , sum(count(basicpolinfo.polid)) on (order customer.custno rows between unbounded preceding , current row ) totalamount customer inner join basicpolinfo on customer.custid = basicpolinfo.custid basicpolinfo.polid not null , customer.firstname not null , customer.entereddate > '1/1/79' group customer.custno , customer.firstname , customer.lastname , customer.entereddate order customer.entereddate asc note while don't care order, order by required in order determine rows precede current row.
