sum and add - 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

General SQL Server Forums

New to SQL Server Programming

sum and add

Author

Topic

baxterstockman
Starting Member

6 Posts

Posted - 2011-02-28 : 14:02:31

hi,

i'm new to sql maybe one of you can help me

i got a table with values and want a column that sum that column
example:
Date value value total

1.1.10 5 5

1.2.10 4 9

1.3.10 1 10

what do i have to do to get the "value total" values?
if i try sum() i only get one column with the grand total, but i want a total for every day ?

can anybody help me?

shelbyoh
Starting Member

14 Posts

Posted - 2011-02-28 : 14:09:21

What is the table name and columns

example
Tb1
Date
Value
Value Total

select * from Tb1
sum (value) as 'value total'
where Date between '2011-01-01 and '2011-01-31'

Here is something to look at

http://www.sqlusa.com/bestpractices/percentonbase/

or

Select * from Tb1
sum (value) as 'value'
sum (value_total) as 'value total'
group by Date

your question is unclear. Please retype

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-02-28 : 14:47:38

Looks like what you are trying to do is compute a running total. If you are using SQL 2005 or above, the following query would give you the running total. I am assuming that the date column is one of the datetime data types. If not, this would not work as expected.

select
	a.Date,
	a.value,
	b.[Value total]
from
	YourTable a
	cross apply (select sum(value) as [Value total] from YourTable b where b.date <= a.date) b

baxterstockman
Starting Member

6 Posts

Posted - 2011-02-28 : 16:56:48

thank you sunitabeck

thats exactly what i wanted, is there a possibilty to get that between a special date

for example

Date value value total

January 1,2011 5 5
January 2,2011 4 9
January 3,2011 1 10
January 4,2011 6 16

so with your query and

WHERE Date between '01-02-2011' and '01-3-2011'

i want to get this one back:
Date value value total

January 2,2011 4 4
January 3,2011 1 5

but in fact i get this one back

Date value value total

January 2,2011 4 9
January 3,2011 1 10

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-02-28 : 20:21:27

You will need to use the date range in the outer query and the inner query as in

select
	a.Date,
	a.value,
	b.[Value total]
from
	YourTable a
	cross apply 
	(
		select sum(value) as [Value total] from YourTable b where b.date <= a.date
			and b.date between '1/2/2011' and '1/3/2011' 
	) b
where
	a.date between '1/2/2011' and '1/3/2011'

baxterstockman
Starting Member

6 Posts

Posted - 2011-03-01 : 12:57:03

thx a lot, that works !

baxterstockman
Starting Member

6 Posts

Posted - 2011-03-08 : 12:01:37

ok and how does it work if i want to combine two columns of two different tables:

for example:
Table 1
Date value1

1.1.10 5

1.2.10 4

1.3.10 1

Table 2
Date value2
1.1.10 3

1.2.10 2

Now i want to add the two values of the different table and then start a running total so that this comes out :

Date value1 value2 runningtotal
1.1.10 5 3 8
1.2.10 4 2 14

i tried it with

select
a.Date,
a.value1,
b.value2
c.runningtotal

from
Table1 a, table2c
cross apply
(
select sum(value) as runngingtotal from YourTable b where b.date <= a.date
and b.date between '1/2/2011' and '1/3/2011'
) b
where
a.date between '1/2/2011' and '1/3/2011'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources