Sum() without aggregation function

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() without aggregation function

Author

Topic

Nova
Starting Member

2 Posts

Posted - 2014-11-07 : 14:34:40

I'm trying to write sum function in SQL with only basic operators without using aggregation function, but I don't know how or if it's possible or not!? I've searched but can't find anything on the internet

For example we have table order:

OrderID ProductID Quantity
----------------------------
1001 15 5
1002 35 7
1002 10 10
1003 50 30
1004 47 15
We can sum up the quantity with sum function in sql

SELECT SUM(Quantity) FROM OrderTable

How can I get the total quantity without using any aggregate functions?

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-07 : 15:04:11

dunno why you would want to, but here goes:



DECLARE cur CURSOR
    FOR SELECT quantity
          FROM mytable;

OPEN cur;
DECLARE @total int = 0
      , @qty int;

FETCH NEXT FROM cur INTO @qty;
WHILE @@fetch_status = 0
    BEGIN
        SET @total+=@qty;
        FETCH NEXT FROM cur INTO @qty;
    END;

CLOSE cur;
DEALLOCATE cur;

PRINT @total;

Nova
Starting Member

2 Posts

Posted - 2014-11-07 : 15:31:10

That’s perfect, Thanks gbritton

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2014-11-26 : 02:08:35

Why do you want to get SUM without using SUM function?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources