Accumlated figures in single record

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

Accumlated figures in single record

Author

Topic

sha_agrawal
Starting Member

24 Posts

Posted - 2009-02-24 : 07:58:12

I convey regards to all experts
I am using VB6 and Sql server 2000.I am having a table named DailySale containing date wise sale figures. Let me explain my problem by an eample. Suppose Table DailySale containing records as
Date Item Name Today
------------------------------
24.2.2009 XYZ 10
25.2.2009 xyz 5
24.2.2009 ABC 20
25.2.2009 ABC 50

I want result as on 25.2.09
ItemName ToDay Todate
---------------------------------
XYZ 5 15
ABC 50 70
(Todate column containing accumalted figures.)
I tried
select dt,sum(sale) from DailySale for Dt=<GIVEN DATE> group by itemid
union
select dt,sum(sale) from DailySale for Dt<=<GIVEN DATE> group by itemid
but it gives result as
ItemName ToDay Todate
--------------------------------
XYZ 5
XYZ 15
ABC 50
ABC 70

I want to have today & todate of an item in single record.Please help me.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-24 : 08:05:05

try this
declare @tab table (Date datetime, ItemName varchar(32), Today int)
insert into @tab select
'2-24-2009','XYZ', 10 union all select
'2-25-2009','xyz', 5 union all select
'2-24-2009','ABC', 20 union all select
'2-25-2009','ABC', 50

select itemname,today,(select sum(today) from @tab where itemname= t.itemname)as today from @tab t where date = '2/25/2009'

sha_agrawal
Starting Member

24 Posts

Posted - 2009-02-24 : 08:28:47

Thank you so much for reply.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-24 : 08:29:55

quote:
Originally posted by sha_agrawal

Thank you so much for reply.

welcome

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-24 : 08:44:27

[code]Select t.Itemname,t.Today,m.SUMAMT
from Table t
outer apply
(Select Sum(Today)as SUMAMT
from Table
Where Itemname = t.Itemname
)m
where date = '2/25/2009'[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-24 : 09:41:44

also if sql 2005

SELECT [Item Name],Total,Today
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY [Item Name] ORDER BY Date DESC) AS Seq,
SUM(Today) OVER (PARTITION BY [Item Name]) AS Total,[Item Name],
Today
FROM Table
)t
WHERE t.Seq=1

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-02-24 : 22:37:21

declare @tab table (Date varchar(32), ItemName varchar(32), Today int)
insert into @tab select
'2/24/2009','XYZ', 10 union all select
'2/25/2009','xyz', 5 union all select
'2/24/2009','ABC', 20 union all select
'2/25/2009','ABC', 50

select t.itemname,t.today,t1.todate from @tab t
left join ( select itemname,sum(today) todate from @tab group by itemname) t1 on t1.itemname = t.itemname
where t.date = '2/25/2009'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-25 : 09:19:23

quote:
Originally posted by Nageswar9

declare @tab table (Date varchar(32), ItemName varchar(32), Today int)
insert into @tab select
'2/24/2009','XYZ', 10 union all select
'2/25/2009','xyz', 5 union all select
'2/24/2009','ABC', 20 union all select
'2/25/2009','ABC', 50

select t.itemname,t.today,t1.todate from @tab t
left join ( select itemname,sum(today) todate from @tab group by itemname) t1 on t1.itemname = t.itemname
where t.date = '2/25/2009'

this will return all records not latest one alone for a Name value

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources