# Miscellaneous > SQL Scripts >  looping with a cursor to get grand totals sqlserver

## mhr

I'm trying to loop through a table using a cursor and assigning the values from each row to variables to come up with two grand totals. I'm adding itself plus the calculation to itself to get a grand total variable. There are about 5 record that match the criteria of the cursor. I can view the individual rows values but when i try to utilize adding it to itself to have a grand total variable its passing me a null value. Can anyone tell me what i'm doing wrong. It it looks like it would total the values up but its not. Any help would be appreciated.
Mark 

OPEN cursorPur 
FETCH NEXT FROM cursorPur into @strCUSTID,@strCUSTNAME,
@strCITY,@strSTATE,@dtORDDT,@intQTY,@decDISC,@decU

PRICE 
WHILE @@FETCH_STATUS = 0
BEGIN 
SET @decTOTPAID = @decTOTPAID + ((@intQTY * @decUPRICE) - @decDISC)
SET @intTOTQTY = @intTOTQTY + @intQTY 
FETCH NEXT FROM cursorPur into @strCUSTID, @strCUSTNAME, 
@strCITY, @strSTATE, @dtORDDT, @intQTY, @decDISC,@decUPRICE 
END 
SELECT @strCUSTID AS CUSTID,
@strCUSTNAME AS strCUSTNAME,
@strCITY AS strCITY,
@strSTATE AS strSTATE,
@decTOTPAID AS TOTPAID, 
@intTOTQTY AS TOTALQTY 
CLOSE cursorPur
DEALLOCATE cursorPur

 :Confused:

----------


## nicc777

Please forgive me if I understood you wrong, but will the following help:

I have a table:



```
CREATE TABLE values (
  id int(10) unsigned NOT NULL auto_increment,
  v1 int(11) default NULL,
  v2 int(11) default NULL,
  v3 int(11) default NULL,
  v4 int(11) default NULL,
  v5 int(11) default NULL,
  PRIMARY KEY  (id)
) TYPE=MyISAM;
```

*Note:* Example is from MySQL.

The values I assigned:




```
INSERT INTO `values` VALUES (1, 5, 10, NULL, NULL, 15),
(2, 10, 5, NULL, NULL, NULL),
(3, 5, 5, 5, 5, 5);
```

Now, I added Columns 1 and 2 in one group, and then 4 and 5 in another group:



```
mysql> SELECT id,(SUM(v1)+SUM(v2) ) AS STotal1, (SUM(v4)+SUM(v5)) AS STotal2 FROM `values` group by 1;
+----+---------+---------+
| id | STotal1 | STotal2 |
+----+---------+---------+
|  1 |      15 |      15 |
|  2 |      15 |       0 |
|  3 |      10 |      10 |
+----+---------+---------+
3 rows in set (0.02 sec)

mysql> SELECT id,(v1+v2 ) AS STotal1, (v4+v5) AS STotal2 FROM `values` group by 1;
+----+---------+---------+
| id | STotal1 | STotal2 |
+----+---------+---------+
|  1 |      15 |    NULL |
|  2 |      15 |    NULL |
|  3 |      10 |      10 |
+----+---------+---------+
3 rows in set (0.00 sec)
```

As you can see, if I just add the values, fields that contain a NULL value will always make the end result a NULL, but if I use SUM ( weird - I know ), this doesn't happen.

Can this be an explination for your situation?

Cheers

----------


## gvraghuramraju

Isnull will solve ur problem

It is as follows

OPEN cursorPur 
FETCH NEXT FROM cursorPur into @strCUSTID,@strCUSTNAME,
@strCITY,@strSTATE,@dtORDDT,@intQTY,@decDISC,@decU


PRICE 
WHILE @@FETCH_STATUS = 0
BEGIN 
SET @decTOTPAID = ISNULL(@decTOTPAID,0) + ((ISNULL(@intQTY,0) * ISNULL(@decUPRICE,0)) - ISNULL(@decDISC,0))
SET @intTOTQTY = ISNULL(@intTOTQTY,0) + ISNULL(@intQTY,0) 
FETCH NEXT FROM cursorPur into @strCUSTID, @strCUSTNAME, 
@strCITY, @strSTATE, @dtORDDT, @intQTY, @decDISC,@decUPRICE 
END 
SELECT @strCUSTID AS CUSTID,
@strCUSTNAME AS strCUSTNAME,
@strCITY AS strCITY,
@strSTATE AS strSTATE,
@decTOTPAID AS TOTPAID, 
@intTOTQTY AS TOTALQTY 
CLOSE cursorPur
DEALLOCATE cursorPur

----------


## nicc777

Ah - MySQL also has ISNULL(), and it will work too  :Smilie: 

Cheers

----------

