Avatar of robrodp
robrodp
Flag 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)
Microsoft SQL Server

Avatar of undefined
Last Comment
robrodp

8/22/2022 - Mon
Steve Wales

Unless I'm completely missing something ....

select name,sales1,sales2,sales3, sales1+sales2+sales3 as TotalSales
from yourtable
where whatever = whatever
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
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
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Steve Wales

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 Wales

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 ....
robrodp

ASKER
Righr it is a query using a pivot that determones which colums show. I identify the columns in the fivot statement
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
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
Steve Wales

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.
robrodp

ASKER
A2010,A2011,A2012 are not fields in tha tbale but values, for the pivot ro work
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Steve Wales

What does your table look like ?  And a couple of sample rows of dummy data?
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
Steve Wales

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Olaf Doschke

>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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
robrodp

ASKER
Sure does. I never thought of it that way

Thx