Add columns in a table

[t_mcgee09]

Hi everyone,

I have a database that looks something like this:

[B]Name       Week1           Week2       Week3[/B]
user1         10             15              7
user2          3              24              2

I need to write an sql select statement that returns 2 columns: the Name column and a Total column which totals that users points for weeks 1 through 3. Is this possible? How can i do it?

Please help! I've been stuck for awhile!

[sknake]

Select Name, (IsNull(Week1, 0) + IsNull(Week2, 0) + IsNull(Week3, 0)) As Total
From TableName
Order By Name

[t_mcgee09]

ok thank you. but lets say I have an extra field...like month:

Name      Month       Week1       Week2      Week3
user1      May          3           4           6
user1      June         2           3           4
user2      May          3           3           3
user2      June                     3           1

And I want to select from the table so I get a result set like this:

Name     May    June
user1     13       9
user2     9        4

Is this possible?

[sknake]

IF OBJECT_ID('Totals', 'U') IS NOT NULL DROP TABLE Totals
Create Table Totals
(
  Name varchar(30),
  Month varchar(30),
  Week1 int,
  Week2 int,
  Week3 int
)

GO

Insert Into Totals (Name, Month, Week1, Week2, Week3) Values ('user1', 'May', 20, 30, 40)
Insert Into Totals (Name, Month, Week1, Week2, Week3) Values ('user1', 'Jun', 12, 43, 11)
Insert Into Totals (Name, Month, Week1, Week2, Week3) Values ('user1', 'Jul', 33, 96, 43)

Insert Into Totals (Name, Month, Week1, Week2, Week3) Values ('user2', 'May', 19, 30, 40)
Insert Into Totals (Name, Month, Week1, Week2, Week3) Values ('user2', 'Jun', 12, 43, 61)
Insert Into Totals (Name, Month, Week1, Week2, Week3) Values ('user2', 'Jul', 38, 26, 43)

GO

Select Name,
Sum((Case When Month = 'May' Then Week1+Week2+Week3 Else 0 End)) As May,
Sum((Case When Month = 'Jun' Then Week1+Week2+Week3 Else 0 End)) As Jun,
Sum((Case When Month = 'Jul' Then Week1+Week2+Week3 Else 0 End)) As Jul
From Totals
Group By Name

[t_mcgee09]

IF OBJECT_ID('Totals', 'U') IS NOT NULL DROP TABLE Totals
Create Table Totals
(
  Name varchar(30),
  Month varchar(30),
  Week1 int,
  Week2 int,
  Week3 int
)

GO

Insert Into Totals (Name, Month, Week1, Week2, Week3) Values ('user1', 'May', 20, 30, 40)
Insert Into Totals (Name, Month, Week1, Week2, Week3) Values ('user1', 'Jun', 12, 43, 11)
Insert Into Totals (Name, Month, Week1, Week2, Week3) Values ('user1', 'Jul', 33, 96, 43)

Insert Into Totals (Name, Month, Week1, Week2, Week3) Values ('user2', 'May', 19, 30, 40)
Insert Into Totals (Name, Month, Week1, Week2, Week3) Values ('user2', 'Jun', 12, 43, 61)
Insert Into Totals (Name, Month, Week1, Week2, Week3) Values ('user2', 'Jul', 38, 26, 43)

GO

Select Name,
Sum((Case When Month = 'May' Then Week1+Week2+Week3 Else 0 End)) As May,
Sum((Case When Month = 'Jun' Then Week1+Week2+Week3 Else 0 End)) As Jun,
Sum((Case When Month = 'Jul' Then Week1+Week2+Week3 Else 0 End)) As Jul
From Totals
Group By Name

Is there any way to do it without creating another table?

[sknake]

You can just use the SELECT query portion on your table. I provided the CREATE TABLE syntax to mimic the table structure you provided me. I was just trying to give you an "out of the box runnable example". Just take this query and modify it to your table name:

Select Name,
Sum((Case When Month = 'May' Then Week1+Week2+Week3 Else 0 End)) As May,
Sum((Case When Month = 'Jun' Then Week1+Week2+Week3 Else 0 End)) As Jun,
Sum((Case When Month = 'Jul' Then Week1+Week2+Week3 Else 0 End)) As Jul
From Totals
Group By Name

[t_mcgee09]

awesome, thank you!

[sknake]

You're welcome.

Please mark this thread as solved if you have found an answer to your question and good luck!

[mail2saion]

I think pivot is the best solution in this regard.