Link to home
Start Free TrialLog in
Avatar of robrodp
robrodpFlag for Mexico

asked on

ms sql 2005 query to add field values

I have a table

name,sales1,sales2,sales3

I need a query that will result in

name,sales1,sales2,sales3,totalsales

for each record (totsales=sales1+sales2+sales3)
Avatar of Steve Wales
Steve Wales
Flag of United States of America image

Unless I'm completely missing something ....

select name,sales1,sales2,sales3, sales1+sales2+sales3 as TotalSales
from yourtable
where whatever = whatever
Avatar of robrodp

ASKER

Problem is the sales1,sales2,sales3 is variablee so what I need is a quesry that will take the last 3 colums and presnet them inthe last column
Avatar of robrodp

ASKER

The names of the colums vary... they are always numbers.

Ca be attendance for 3 non consecutive months

name,attendancemay,attendancejuly,attendancedecember,totalattendance
I still don't understand your issue.  See below:
create table mytab (name char(10), sales1 int, sales2 int, sales3 int);
go
insert into mytab values('Guy 1',1,2,3);

(1 row(s) affected)

insert into mytab values('Guy 2',4,5,6);

(1 row(s) affected)

select * from mytab;

name       sales1      sales2      sales3
---------- ----------- ----------- -----------
Guy 1      1           2           3
Guy 2      4           5           6

(2 row(s) affected)

select name, sales1, sales2, sales3, sales1+sales2+sales3 as TotalSales
from mytab;

name       sales1      sales2      sales3      TotalSales
---------- ----------- ----------- ----------- -----------
Guy 1      1           2           3           6
Guy 2      4           5           6           15

(2 row(s) affected)

Open in new window

Ah, OK, now I understand.

If the columns you're selecting change, how are you building the original query - dynamic SQL ?

You have to be able to identify the columns you're using somewhere ....
Avatar of robrodp

ASKER

Righr it is a query using a pivot that determones which colums show. I identify the columns in the fivot statement
Avatar of robrodp

ASKER

For example:

SELECT *
FROM (
   SELECT [periodo], [armadora],value
   FROM parquevx
) f
PIVOT (
   sum([value])
   FOR [periodo]
   IN (A2010,A2011,A2012)
) p
ORDER BY [A2012] desc

I need the total of A210,A2011,A2012) to appera at the end for the query. One single query would be fantastic
You should be able to do something like:

SELECT periodo, armadora, value, A2010, A2011, A2012, A2010+A2011+A2012 as TotalValue
FROM (
   SELECT [periodo], [armadora],value
   FROM parquevx
) f
PIVOT (
   sum([value])
   FOR [periodo]
   IN (A2010,A2011,A2012)
) p
ORDER BY [A2012] desc

Open in new window


Without having the table layouts and what not handy I'm not 100% certain that the syntax there is correct but I think it should be close.

What does the output from your original query look like ?

Replace "Select *" with select all those columns and then add in the addition of the three columns you need for your total and that should be it.
Avatar of robrodp

ASKER

A2010,A2011,A2012 are not fields in tha tbale but values, for the pivot ro work
What does your table look like ?  And a couple of sample rows of dummy data?
Avatar of robrodp

ASKER

The table has these fields:

name.Year,Sales there are 1,000,records the pivot query gives a result of

Records are like (3 records)

name john
year year1
value 123

name john
year year2
value 234

name john
year year3
value 125

The query resutlt is:

name, year1,year2,year3
john,123,234,125

I need a query that renders:
name, year1,year2,year3,total
john,123,234,125,483
ASKER CERTIFIED SOLUTION
Avatar of Steve Wales
Steve Wales
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
>The names of the columns vary... they are always numbers.

Several things are still unclear. Do you have different tables with the same data typed columns? Do the tables have more columns, eg in your case about the monthly attendances, and the user should be able to pick any three of them?
Are these tables you want to process this way simply normal database table, or are they coming out from previous data processing as table variable, temp tables, CTE or query results, eg stored proc results.

You have several things at hand, eg EXEC sp_columns 'Tablename' gives you column names of a table and you see that's not possible this way for a cte or query result or table variable. If it's about tables, you have a deign problem, but anyway, you can then put together a query string in a varchar(MAX) variable called @sql to execute by exec sp_executesql @sql.

Actually it's common to not do a row sum, but transpose data or even keep it in one value per row and accumulate.

eg having data in the form [person, attendancedatetime, attendancevalue] you can easily SUM(attendancevalue) for each person and also filter for three months, eg via MONTH(attendancedatetime) in (5,7,12) and YEAR(attendancedatetime)=YEAR(GetDate())-1 for may, july and december of the past year:

SELECT personid, SUM(attendancevalue)
FROM originalattendancedata
WHERE MONTH(attendancedatetime) in (5,7,12) and YEAR(attendancedatetime)=YEAR(GetDate())-1
GROUP BY personid

What's your more general goal to accomplish? I think it can be done much simpler not forcing a route that isn't foreseen in SQL at least in an elegant way. As so often, it's also a question of your database design.

Bye, Olaf.
Avatar of robrodp

ASKER

Sure does. I never thought of it that way

Thx