Link to home
Start Free TrialLog in
Avatar of CaptainGiblets
CaptainGibletsFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Calculations in views, or something similar

Is it possible for me to create another column in a view and then calculate other columns in that view?

If not is there any other way i can achieve this?

Cheers in advance.
Avatar of momi_sabag
momi_sabag
Flag of United States of America image

sure it's possible
you can also use a calculated column inside a table
what exactly do you need ?
Avatar of CaptainGiblets

ASKER

well i have 12 columns called month 1 - 12

i want to calculate them all for a total

then i want to multiply another column called mmsc by 12, and   then do a   total - mmsc
ASKER CERTIFIED SOLUTION
Avatar of momi_sabag
momi_sabag
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
i managed to get it working after your response. I looked around a bit more on the internet and noticed my "Group By" column was hidden, which was fooling me as i couldnt find the 'sum' field

i managed to get it working using the code

(dbo.tbl_revenue.[Month 2] + dbo.tbl_revenue.[Month 1] + dbo.tbl_revenue.[Month 3] + dbo.tbl_revenue.[Month 4] + dbo.tbl_revenue.[Month 5] + dbo.tbl_revenue.[Month 6] + dbo.tbl_revenue.[Month 7] + dbo.tbl_revenue.[Month 8] + dbo.tbl_revenue.[Month 9] + dbo.tbl_revenue.[Month 10] + dbo.tbl_revenue.[Month 11] + dbo.tbl_revenue.[Month 12]) - dbo.tblcommission.[Projected Monthly MSC] * 12

in the query editor.

thanks a lot for the help, However i have 1 final question.

Is there a way i can refer to an alias in the code? as i tried to break my sum down into 3 different parts

the months and their totals
the projeted monthly msc * 12
and then the difference of the 2

however i couldnt get it refering to the alias i set the projected monthly msc (test1234)

i coded it like

totalmsc - test1234

and it wouldnt work for me.
do you mean inside the code of the view itself?
you can do this

create view ... as

select total,  total-12*mmsc
from (
select all-your-columns-come-here,  month1+month2+ ... + month12 as total
 from your_table
) as t

is that what you meant?
by wrapping the select inside a derived table expression, you can referer to the aliases in the outside query
what i mean is when i assign a sum to a column in the query editor, it lets you choose an "as" so the name to display it as.

Is there a way to refer to the "as" name in other calculations?

I have the attatched code now and it wont let me select the "as" name test.
SELECT     dbo.tbl_revenue.MID, dbo.tbl_revenue.[Trading As Name], dbo.tbl_revenue.[Date Active], dbo.tbl_revenue.[Commission Code], dbo.tbl_revenue.[Month 1], 
                      dbo.tbl_revenue.[Month 2], dbo.tbl_revenue.[Month 3], dbo.tbl_revenue.[Month 4], dbo.tbl_revenue.[Month 5], dbo.tbl_revenue.[Month 6], dbo.tbl_revenue.[Month 7], 
                      dbo.tbl_revenue.[Month 8], dbo.tbl_revenue.[Month 9], dbo.tbl_revenue.[Month 10], dbo.tbl_revenue.[Month 11], dbo.tbl_revenue.[Month 12], 
                      dbo.tblcommission.[Projected Monthly MSC], 
                      SUM((dbo.tbl_revenue.[Month 2] + dbo.tbl_revenue.[Month 1] + dbo.tbl_revenue.[Month 3] + dbo.tbl_revenue.[Month 4] + dbo.tbl_revenue.[Month 5] + dbo.tbl_revenue.[Month 6]
                       + dbo.tbl_revenue.[Month 7] + dbo.tbl_revenue.[Month 8] + dbo.tbl_revenue.[Month 9] + dbo.tbl_revenue.[Month 10] + dbo.tbl_revenue.[Month 11] + dbo.tbl_revenue.[Month 12])
                       - dbo.tblcommission.[Projected Monthly MSC] * 12) AS Total
FROM         dbo.tblcommission INNER JOIN
                      dbo.tblterminaldetails ON dbo.tblcommission.Auto = dbo.tblterminaldetails.Auto INNER JOIN
                      dbo.tbl_revenue ON dbo.tblterminaldetails.MID = dbo.tbl_revenue.MID
GROUP BY dbo.tbl_revenue.MID, dbo.tbl_revenue.[Trading As Name], dbo.tbl_revenue.[Date Active], dbo.tbl_revenue.[Commission Code], dbo.tbl_revenue.[Month 1], 
                      dbo.tbl_revenue.[Month 2], dbo.tbl_revenue.[Month 3], dbo.tbl_revenue.[Month 4], dbo.tbl_revenue.[Month 5], dbo.tbl_revenue.[Month 6], dbo.tbl_revenue.[Month 7], 
                      dbo.tbl_revenue.[Month 8], dbo.tbl_revenue.[Month 9], dbo.tbl_revenue.[Month 10], dbo.tbl_revenue.[Month 11], dbo.tbl_revenue.[Month 12], 
                      dbo.tblcommission.[Projected Monthly MSC]

Open in new window

Avatar of Mark Wills
Not directly...

but you could wrap it up as a subquery, then you can access those column names... e.g.


select *
from
(SELECT     dbo.tbl_revenue.MID, dbo.tbl_revenue.[Trading As Name], dbo.tbl_revenue.[Date Active], dbo.tbl_revenue.[Commission Code], dbo.tbl_revenue.[Month 1],
                      dbo.tbl_revenue.[Month 2], dbo.tbl_revenue.[Month 3], dbo.tbl_revenue.[Month 4], dbo.tbl_revenue.[Month 5], dbo.tbl_revenue.[Month 6], dbo.tbl_revenue.[Month 7],
                      dbo.tbl_revenue.[Month 8], dbo.tbl_revenue.[Month 9], dbo.tbl_revenue.[Month 10], dbo.tbl_revenue.[Month 11], dbo.tbl_revenue.[Month 12],
                      dbo.tblcommission.[Projected Monthly MSC],
                      SUM((dbo.tbl_revenue.[Month 2] + dbo.tbl_revenue.[Month 1] + dbo.tbl_revenue.[Month 3] + dbo.tbl_revenue.[Month 4] + dbo.tbl_revenue.[Month 5] + dbo.tbl_revenue.[Month 6]
                       + dbo.tbl_revenue.[Month 7] + dbo.tbl_revenue.[Month 8] + dbo.tbl_revenue.[Month 9] + dbo.tbl_revenue.[Month 10] + dbo.tbl_revenue.[Month 11] + dbo.tbl_revenue.[Month 12])
                       - dbo.tblcommission.[Projected Monthly MSC] * 12) AS Total
FROM         dbo.tblcommission INNER JOIN
                      dbo.tblterminaldetails ON dbo.tblcommission.Auto = dbo.tblterminaldetails.Auto INNER JOIN
                      dbo.tbl_revenue ON dbo.tblterminaldetails.MID = dbo.tbl_revenue.MID
GROUP BY dbo.tbl_revenue.MID, dbo.tbl_revenue.[Trading As Name], dbo.tbl_revenue.[Date Active], dbo.tbl_revenue.[Commission Code], dbo.tbl_revenue.[Month 1],
                      dbo.tbl_revenue.[Month 2], dbo.tbl_revenue.[Month 3], dbo.tbl_revenue.[Month 4], dbo.tbl_revenue.[Month 5], dbo.tbl_revenue.[Month 6], dbo.tbl_revenue.[Month 7],
                      dbo.tbl_revenue.[Month 8], dbo.tbl_revenue.[Month 9], dbo.tbl_revenue.[Month 10], dbo.tbl_revenue.[Month 11], dbo.tbl_revenue.[Month 12],
                      dbo.tblcommission.[Projected Monthly MSC]
 
) as MyList