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

[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

Cheers