Need a Sql Querry - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Need a Sql Querry

Author

Topic

rbharatha
Starting Member

29 Posts

Posted - 2004-02-02 : 21:57:49

Hi All:

I've 3 columns in a table, col1, col2, col3 where I required to add the col3 value and display in new col4 (which is cummlative total of col3).

for ex: I Need a select query to display in the following order, how do write the sql.

COL1 COL2 COL3 COL4
1 A A1 1 1
2 B B1 2 3 ( COL3 ROW1 + COL3 ROW2)
3 C C1 3 6 ( COL3 ROW1 + COL3 ROW2 + COL3 ROW3)
4 D D1 10 16 ( COL3 ROW1 + COL3 ROW2 + COL3 ROW3 + COL3 ROW4)

thanks in advance.

Ramesh

SamC
White Water Yakist

3467 Posts

Posted - 2004-02-02 : 22:58:16

There's no inherent order in SQL tables and you need an order for this problem.

Here's an example which assumes Col1 is an ordering column:

SELECT Col1, Col2, Col3, (SELECT SUM(Col3) From MyTable A WHERE A.Col1 <= Col1) As Subtotal
FROM MyTable
ORDER BY Col1

rbharatha
Starting Member

29 Posts

Posted - 2004-02-02 : 23:10:32

Hi SamC

The Query is used to sum the Total of the col3 but adding the Cummulative Total of Col3, I require to do addition of Col3 of Row1 and put in in Col4 then for the record I have to add Col4 of row1 + Col3 of Row2 ...... and not order by as in your case.

ROW COL1 COL2 COL3 COL4 (Cummulative Total)
1 A A1 3 3 ( COL3 ROW1)
2 B B1 2 5 ( COL3 ROW1 + COL3 ROW2)
3 C C1 8 13 ( COL3 ROW1 + COL3 ROW2 + COL3 ROW3)
4 D D1 10 23 ( COL3 ROW1 + COL3 ROW2 + COL3 ROW3 + COL3 ROW4)

SamC
White Water Yakist

3467 Posts

Posted - 2004-02-03 : 00:17:47

quote:
and not order by as in your case.

Well, the query calculates the cumulative subtotal, but only if it is ordered.

Without an order to the resultset, you might be able to calculate the result using a CURSOR, but it will be slower.

rbharatha
Starting Member

29 Posts

Posted - 2004-02-03 : 00:41:26

Hi All;

ROW COL1 COL2 COL3 COL4 (Cummulative Total)
1 A A1 3 3 ( COL3 ROW1)
2 B B1 2 5 ( COL3 ROW1 + COL3 ROW2)
3 C C1 8 13 ( COL3 ROW1 + COL3 ROW2 + COL3 ROW3)
4 D D1 10 23 ( COL3 ROW1 + COL3 ROW2 + COL3 ROW3 + COL3 ROW4)

This is Table from which, I wana query to do Cummulative Total of the col3 and put in in Col4 as shown in the above format, In what ever way you can do it.

thks

Ramesh

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-02-03 : 05:39:05

You're missing SAMs point....ROW1 is ONLY RELATED to ROW2 in SQL....by virture of it being SORTED (ORDERED) before ROW2....there is no inherant relationship between ROW1 + ROW2 (or none from your description anyway)

You cannot write a query that will guarantee the predicted/right result EVERY time, without putting an ORDER BY on the SQL statement. SQL has no concept of the first/2nd/last record in a table...basically all records are in the table and all can be first/2nd/last in the resultset....based on the execution path of the SQL.

Take for instance your data....
1 A A1 3 3
2 B B1 2 5
3 C C1 8 13
4 D D1 10 23

now reorder (renumber, and re-calculate col3) your input set....to derive a new output resultset
1 B B1 2 2
2 A A1 3 5
3 D D1 10 15
4 C C1 8 23

Is this a valid result?....or MUST the answer for col3 on ROW "A A1" be 3 and ROW "D D1" be 23? (etc)

Where 2 ROWS in a resultset have a DEFINED relationship BASED on their position in the resultset(and possibly have a calculation based upon that relative position)....THEN YOU MUST HAVE an ORDER BY statement in the SQL.

Search here for 'oracle sequence/row number'....and you'll see this position clarified furthur...in some of the topics mentioned.

HendersonToo
Starting Member

20 Posts

Posted - 2004-02-03 : 15:41:06

The poster might be confused because the query didn't result in what he asked for. Indeed punch it in, run it, you'll see you end up with the following result from this query supplied by SamC:


SELECT Col1, Col2, Col3, (
    SELECT SUM(Col3) 
    FROM MyTable A 
    WHERE A.Col1 <= Col1) As Subtotal
FROM MyTable
ORDER BY Col1

A A1  3  23 
B B1  2  23 
C C1  8  23 
D D1 10  23

instead of

This is a real solution to the problem of including a sum as a column (and some other aggregrates) calculated from the field of resultset your querying.

note that modifying the query:


SELECT Col1, Col2, Col3, (SELECT SUM(Col3) From MyTable A) As Subtotal
FROM MyTable
ORDER BY Col1

produces the same resultset.

I took a whack at this, and couldn't think of how I would accomplish this for n records, short of using a cursor. Self-joining seems like the right way, but I can't nail it today...

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-02-03 : 16:10:38

This is indeed a runs and streaks type of solution and Sam has the right idea. Probably just a typo in the streak query.

create table mytable (col1 char(1),col2 char(2),col3 int)

insert into mytable
select 'A', 'A1',  3 
union all select 'B', 'B1',  2
union all select 'C', 'C1',  8
union all select 'D', 'D1', 10

select * , (select sum(col3) from mytable where col1 <= m.col1) col4
from mytable m
order by col1

drop table mytable

EDIT: Added Link: http://www.sqlteam.com/item.asp?ItemID=12654

SamC
White Water Yakist

3467 Posts

Posted - 2004-02-03 : 20:04:16

Sorry all. I blew the column reference and Jay got it right.

Sam

rbharatha
Starting Member

29 Posts

Posted - 2004-02-03 : 21:58:14

Hi Sam Carter:
Correct Query with reference requried to the parent table, which solved the issue.
SELECT Col1, Col2, Col3, (SELECT SUM(Col3) From MyTable A WHERE A.COL1 <= B.COL1) As Col4 FROM MyTable B ORDER BY Col1

In your query reference to the parent was not taking, when i run as default. b'z both queries are similar but only diff is Alias Name given to the parent table.

SELECT Col1, Col2, Col3, (SELECT SUM(Col3) From MyTable A WHERE A.Col1 <= Col1) As Subtotal FROM MyTable ORDER BY Col1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources