SUMMATION - 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

SUMMATION

Author

Topic

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-03-28 : 20:11:10

How can I get the sum of the query results of these formula:

SELECT dbo.TechphonesCalibQA.ID, dbo.TechphonesCalibQA.EmpID, dbo.TechphonesCalibQA.EmpName, dbo.TechphonesCalibQA.[Level],
dbo.TechphonesCalibQA.Coach, dbo.TechphonesCalibQA.Queue, dbo.TechphonesCalibQA.PreferredName, dbo.TechphonesCalibQA.SesID,
dbo.TechphonesCalibQA.SesDate, dbo.TechphonesCalibQA.SesTime, dbo.TechphonesCalibQA.SesLen,
Case dbo.TechphonesCalibQA.G_Branded when dbo.TechphonesCalibClient.G_Branded then 1 else 0 end as G_Branded,
Case dbo.TechphonesCalibQA.G_PIVer when dbo.TechphonesCalibClient.G_PIVer then 1 else 0 end as G_PIVer,
Case dbo.TechphonesCalibQA.G_AskMemb when dbo.TechphonesCalibClient.G_AskMemb then 1 else 0 end as G_AskMemb,
Case dbo.TechphonesCalibQA.G_AskCall when dbo.TechphonesCalibClient.G_AskCall then 1 else 0 end as G_AskCall,
Case dbo.TechphonesCalibQA.G_UsedSend when dbo.TechphonesCalibClient.G_UsedSend then 1 else 0 end as G_UsedSend,
Case dbo.TechphonesCalibQA.V_Privacy when dbo.TechphonesCalibClient.V_Privacy then 1 else 0 end as V_Privacy,
Case dbo.TechphonesCalibQA.V_Followed when dbo.TechphonesCalibClient.V_Followed then 1 else 0 end as V_Followed,
Case dbo.TechphonesCalibQA.V_Changed when dbo.TechphonesCalibClient.V_Changed then 1 else 0 end as V_Changed,
Case dbo.TechphonesCalibQA.D_UseEffect when dbo.TechphonesCalibClient.D_UseEffect then 1 else 0 end as D_UseEffect,
Case dbo.TechphonesCalibQA.D_ConConfi when dbo.TechphonesCalibClient.D_ConConfi then 1 else 0 end as D_ConConfi,
Case dbo.TechphonesCalibQA.D_PropDoc when dbo.TechphonesCalibClient.D_PropDoc then 1 else 0 end as D_PropDoc,
Case dbo.TechphonesCalibQA.D_UseKb when dbo.TechphonesCalibClient.D_UseKb then 1 else 0 end as D_UseKb,
Case dbo.TechphonesCalibQA.S_Fix when dbo.TechphonesCalibClient.S_Fix then 1 else 0 end as S_Fix,
Case dbo.TechphonesCalibQA.S_Recorded when dbo.TechphonesCalibClient.S_Recorded then 1 else 0 end as S_Recorded,
Case dbo.TechphonesCalibQA.S_Provided when dbo.TechphonesCalibClient.S_Provided then 1 else 0 end as S_Provided,
Case dbo.TechphonesCalibQA.C_Askaddit when dbo.TechphonesCalibClient.C_Askaddit then 1 else 0 end as C_Askaddit,
Case dbo.TechphonesCalibQA.C_Stated when dbo.TechphonesCalibClient.C_Stated then 1 else 0 end as C_Stated,
Case dbo.TechphonesCalibQA.AB_PresntInfo when dbo.TechphonesCalibClient.AB_PresntInfo then 1 else 0 end as AB_PresntInfo,
Case dbo.TechphonesCalibQA.AB_ResMbrTym when dbo.TechphonesCalibClient.AB_ResMbrTym then 1 else 0 end as AB_ResMbrTym,
Case dbo.TechphonesCalibQA.AB_Respond when dbo.TechphonesCalibClient.AB_Respond then 1 else 0 end as AB_Respond,
Case dbo.TechphonesCalibQA.AB_Appsol when dbo.TechphonesCalibClient.AB_Appsol then 1 else 0 end as AB_Appsol,
Case dbo.TechphonesCalibQA.AB_Adjustsp when dbo.TechphonesCalibClient.AB_Adjustsp then 1 else 0 end as AB_Adjustsp,
Case dbo.TechphonesCalibQA.AB_respect when dbo.TechphonesCalibClient.AB_respect then 1 else 0 end as AB_respect,
Case dbo.TechphonesCalibQA.AB_MuteOcur when dbo.TechphonesCalibClient.AB_MuteOcur then 1 else 0 end as AB_MuteOcur,
Case dbo.TechphonesCalibQA.AB_HoldOcur when dbo.TechphonesCalibClient.AB_HoldOcur then 1 else 0 end as AB_HoldOcur,
Case dbo.TechphonesCalibQA.SS_actlist when dbo.TechphonesCalibClient.SS_actlist then 1 else 0 end as SS_actlist,
Case dbo.TechphonesCalibQA.SS_empathy when dbo.TechphonesCalibClient.SS_empathy then 1 else 0 end as SS_empathy,
Case dbo.TechphonesCalibQA.SS_focus when dbo.TechphonesCalibClient.SS_focus then 1 else 0 end as SS_focus,
Case dbo.TechphonesCalibQA.SS_confi when dbo.TechphonesCalibClient.SS_confi then 1 else 0 end as SS_confi,
Case dbo.TechphonesCalibQA.SS_commun when dbo.TechphonesCalibClient.SS_commun then 1 else 0 end as SS_commun,
Case dbo.TechphonesCalibQA.SS_accent when dbo.TechphonesCalibClient.SS_accent then 1 else 0 end as SS_accent

FROM dbo.TechPhonesCalibClient INNER JOIN
dbo.TechphonesCalibQA ON dbo.TechPhonesCalibClient.SesID = dbo.TechphonesCalibQA.SesID

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-03-28 : 20:14:04

select otherColumns, sum(yourCol1 + yourCol2 + ... + yourColN) as [sum]
from
(
your select here
) t

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-03-28 : 20:27:00

The problem is I want to add or get the sume of the AS results...like for example as SS_accent + as SS_commun, etc..Is that possible?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-03-28 : 20:31:28

yes.
and the problem you're having after trying my suggestion is what?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-03-28 : 20:40:01

It doesn't add since they're fomr "AS' columns.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-03-28 : 21:01:27

show us what you've tried.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-03-28 : 21:16:23

quote:
Originally posted by cutiebo2t

It doesn't add since they're fomr "AS' columns.

Yes it does. If you don't understand how to incorporate spirit's solution, then let us know so that we can explain it. But you should at least try it as it does what you want.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-03-29 : 05:50:39

I'm giving you a simple formula.

Here's the formula:

SELECT SUM(Data1 + Data2) AS result1, SUM(Data3 + Data4) AS result2
FROM Test1

As you can see the results are

result1 = 80
result2 = 30

Now I want another formula and add another column for adding result1 and result2 so I can have a sum of 110

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-03-29 : 11:18:45

Using spirit1's template, we do this:


SELECT result1, result2, SUM(result1 + result2) AS TotalSum
FROM
(
	SELECT SUM(Data1 + Data2) AS result1, SUM(Data3 + Data4) AS result2
	FROM Test1
) t

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-03-29 : 20:25:26

I'm still getting message taht Column't.result1' is invalid in the select list because it is not contained in an aggregrate function and there is no GROUP BY clause

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-03-29 : 23:59:51

The template is a little off which I didn't notice until now. You can only show the totalsum in the select list.

But you can do like this instead by putting min, max, avg, or sum around your other columns since it's already summed:


create table t1 (c1 int, c2 varchar(50), c3 varchar(50), c4 int, c5 int)

insert into t1 values(1, 't', 'z', 2, 1)
insert into t1 values(2, 'aa', 'b', 6, 5)
insert into t1 values(3, 'ra', 'q', 99, 7)

select sum(sum1), sum(sum1+sum2) as totalsum
from
(
	select sum(c1 + c4) as sum1, sum(c5) as sum2
	from t1
) t

drop table t1

It's cheating that way though.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources