View Help and calculations

[Ms Linda]

Good day all! Need assistance with the following:

I a table that houses information and want to create calculations based on the info.
I have created a view to perform one of the calcs.
Now I want to sum columns that will put info into another field that I can than perform another function. Unfortunately I keep getting aggregate errors that I need to add field in group by or select statement. I can do one, but not others. After I do one I then need to take that field and perform another calculation.

Example
SUM (Column A) AS TotalColumnA
SUM (Column B) AS TotalColumnB

Then I need to divide Column A by Column B to get to AS Column C
etc....

Since this is a vew can it be done? To further complicate this I need to be able to choose a "between date" end user fills in the begin date and end date.

YIKES!!! I have manual and have been trying to figure out, but I am brain dead trying many different things.

Your expertise would be very appreicated on how I should set this up in MS SQL. If you need a copy of what I am doing... happy to send along.

Thank you!!!!!!!!!!!!!!!!

[tesuji]

hi,
this could be a solution:

select SUM (Column A) AS TotalColumnA, SUM (Column B) AS TotalColumnB, 
    TotalColumnA / TotalColumnB  as TotalColumnC from yourTable;

If you want to select further columns, for example ColumnX together with results from aggregate functions, you need group by clause:

select ColumnX, SUM (ColumnA) AS TotalColumnA, SUM (ColumnB) 
   AS TotalColumnB, TotalColumnA / TotalColumnB  as TotalColumnC 
   from yourTable group by ColumnX;

Maybe you post your complete SQL select statement, also view definition, possibly I might then be able to give better advice.

krs,
tesu

[Ms Linda]

Thank you so much for quick response!!! I tried your formula and I'm sure it'scorrect, but my dilemma is a bit more complex. I'm not sure if this has something to do with it, but I have created a view to get totals for other columns,and am then creating this other view to pull them together and run totals and new formulas on those totals. I have attached a screen print to show you what I am doing. Since I am fairly new to all of this I hope I'm not in over my head and you can give me some help.

Everything I am trying to do is in a spreadsheet which is very cumbersome and not productive, hence why I am creating a database. If you have any questions you are welcome to email me at [e-mail].

I would be very appreciative for your help.

Again my sincere thanks to put me on the right track! I have a sql book, but it's just not detailed enough to help me or I'm just too plain stupid to understand it! smile...

[tesuji]

Hi again,

well, I have got some problems understanding your screen shots. Wouldn't it be easier you post your sql code for tables and view creations? I know, if select statements are getting more and more complex, creating views or stored procedure is obviously an appropriate vehicle to get some provisional results where further results might be drawn from. Do you ever thought of using WITH clause?

Maybe I will be able to give you further advice when I would have seen the create-view and create-tables code.

krs,
tesu