collecting data from a table - 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

collecting data from a table

Author

Topic

tech426
Starting Member

8 Posts

Posted - 2009-03-09 : 00:34:01

Hi...

I have a table Tiffin. The fields are name, tax, amount, date etc.
I want to get the result as name, sum(tax), sum(amount)of all the dates for a particular month and year (of date column) for each name.

Ie;
Rafel, 25 , 20 , 03/03/2009
Rafel, 25 , 25 , 02/03/2009
Rafel, 25 , 30 , 01/03/2009
Rafel, 25 , 10 , 04/03/2009
Smith, 24, 25, 04/03/2009
Ludovic,35, 26, 03/03/2009

Here, i want to calculate sum(tax), sum(amount) for Rafel for the month 03 (March) and the year 2009. How can i write the query. ?? plz help me..

http://imyideas.blogspot.com

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-09 : 00:37:16

try this once
select name, sum(tax),sum(amount) from tiffin where month(date) = month(getdate()) and year(date) = year(getdate()) group by name

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-03-09 : 00:37:36

user this
where datepart(mm,datecol) = 3 and datepart(yy,datecol) = 2009

tech426
Starting Member

8 Posts

Posted - 2009-03-09 : 00:46:17

Hey.. Thanks a lot for the help !! let me try your suggestions..

http://imyideas.blogspot.com

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-03-09 : 00:47:34

Welcome

Hi Tech426,
Rafel, 25 , 20 , 03/03/2009
Rafel, 25 , 25 , 02/03/2009
Rafel, 25 , 30 , 01/03/2009
Rafel, 25 , 10 , 04/03/2009
Smith, 24, 25, 04/03/2009
Ludovic,35, 26, 03/03/2009

Is the blue color values are date or Months?

tech426
Starting Member

8 Posts

Posted - 2009-03-09 : 00:52:53

They are dates.

http://imyideas.blogspot.com

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-03-09 : 00:55:02

Then Use This

declare @temp table ( name varchar(32), tax int , amount int, date datetime )
insert into @temp
select 'Rafel', 25 , 20 , '03/03/2009' union all
select 'Rafel', 25 , 25 , '03/02/2009' union all
select 'Rafel', 25 , 30 , '03/01/2009' union all
select 'Rafel', 25 , 10 , '03/04/2009' union all
select 'Smith', 24, 25, '03/04/2009' union all
select 'Ludovic',35, 26,'03/03/2009'

select name, sum(tax) Tax,sum(amount) Amount from @temp where month(date) =3 and year(date) = 2009 group by name

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-09 : 00:58:47

did u try my query it will give u required output
which month & year u pass it will give that output data

tech426
Starting Member

8 Posts

Posted - 2009-03-09 : 01:20:06

Sorry for the delayed reply.. I was executing your queries. Yes your query is working for me..

.

http://imyideas.blogspot.com

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-03-09 : 05:13:59

To make use of index if defined, use

declare @temp table ( name varchar(32), tax int , amount int, date datetime )
insert into @temp
select 'Rafel', 25 , 20 , '03/03/2009' union all
select 'Rafel', 25 , 25 , '03/02/2009' union all
select 'Rafel', 25 , 30 , '03/01/2009' union all
select 'Rafel', 25 , 10 , '03/04/2009' union all
select 'Smith', 24, 25, '03/04/2009' union all
select 'Ludovic',35, 26,'03/03/2009'

declare @month int, @year int
select @month=3, @year=2009

select name, sum(tax) Tax,sum(amount) Amount from @temp
where date>=dateadd(month, @month-1,dateadd(year,@year-1900,0)) and date<dateadd(month, @month,dateadd(year,@year-1900,0))
group by name

Madhivanan

Failing to plan is Planning to fail

tech426
Starting Member

8 Posts

Posted - 2009-03-09 : 06:40:54

I didn't define any index. I am bit confused with this temp table. So if i want to insert all my datas to the temp table, how can i write the query. ??

declare @temp table ( name varchar(32), tax int , amount int, date datetime )
insert into @temp

select 'Rafel', 25 , 20 , '03/03/2009' union all
select 'Rafel', 25 , 25 , '03/02/2009' union all
select 'Rafel', 25 , 30 , '03/01/2009' union all
select 'Rafel', 25 , 10 , '03/04/2009' union all
select 'Smith', 24, 25, '03/04/2009' union all
select 'Ludovic',35, 26,'03/03/2009'

Can I write the query as follows?

declare @temp table ( name varchar(32), tax int , amount int, date datetime )
insert into @temp
select name, tax, amount from tiffin

thank you

http://imyideas.blogspot.com

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-09 : 06:46:38

declare @temp table ( name varchar(32), tax int , amount int, date datetime )
insert into @temp
select name, tax, amount,date from tiffin

or if u don't want to insert date
then
insert into @temp (name, tax, amount)
select name, tax, amount from tiffin

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-03-09 : 06:56:25

quote:
Originally posted by tech426

I didn't define any index. I am bit confused with this temp table. So if i want to insert all my datas to the temp table, how can i write the query. ??

declare @temp table ( name varchar(32), tax int , amount int, date datetime )
insert into @temp

select 'Rafel', 25 , 20 , '03/03/2009' union all
select 'Rafel', 25 , 25 , '03/02/2009' union all
select 'Rafel', 25 , 30 , '03/01/2009' union all
select 'Rafel', 25 , 10 , '03/04/2009' union all
select 'Smith', 24, 25, '03/04/2009' union all
select 'Ludovic',35, 26,'03/03/2009'

Can I write the query as follows?

declare @temp table ( name varchar(32), tax int , amount int, date datetime )
insert into @temp
select name, tax, amount from tiffin

thank you

http://imyideas.blogspot.com

Actually you should use

select name, sum(tax) Tax,sum(amount) Amount from tiffin
where date>=dateadd(month, @month-1,dateadd(year,@year-1900,0)) and date<dateadd(month, @month,dateadd(year,@year-1900,0))
group by name

Madhivanan

Failing to plan is Planning to fail

tech426
Starting Member

8 Posts

Posted - 2009-03-09 : 07:58:56

It worked.

http://imyideas.blogspot.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources