We help IT Professionals succeed at work.

ms sql 2005 query to add field values

robrodp
robrodp asked
on
151 Views
Last Modified: 2014-06-17
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)
Comment
Watch Question

Steve WalesSenior Database Administrator
CERTIFIED EXPERT

Commented:
Unless I'm completely missing something ....

select name,sales1,sales2,sales3, sales1+sales2+sales3 as TotalSales
from yourtable
where whatever = whatever
robrodpProgrammer

Author

Commented:
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
robrodpProgrammer

Author

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

Ca be attendance for 3 non consecutive months

name,attendancemay,attendancejuly,attendancedecember,totalattendance
Steve WalesSenior Database Administrator
CERTIFIED EXPERT

Commented:
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

Steve WalesSenior Database Administrator
CERTIFIED EXPERT

Commented:
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 ....
robrodpProgrammer

Author

Commented:
Righr it is a query using a pivot that determones which colums show. I identify the columns in the fivot statement
robrodpProgrammer

Author

Commented:
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
Steve WalesSenior Database Administrator
CERTIFIED EXPERT

Commented:
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.
robrodpProgrammer

Author

Commented:
A2010,A2011,A2012 are not fields in tha tbale but values, for the pivot ro work
Steve WalesSenior Database Administrator
CERTIFIED EXPERT

Commented:
What does your table look like ?  And a couple of sample rows of dummy data?
robrodpProgrammer

Author

Commented:
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
Senior Database Administrator
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Olaf DoschkeSoftware Developer
CERTIFIED EXPERT

Commented:
>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.
robrodpProgrammer

Author

Commented:
Sure does. I never thought of it that way

Thx
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.