How do I sum results columns (grand total) without repeating all the formulas

Question

Post reply

How do I sum results columns (grand total) without repeating all the formulas

Mark Hawkins

Old Hand

Points: 343
More actions
September 21, 2004 at 11:16 pm

#59987

Ive got three columns in a query which are the result of a multiplication of 2 other columns.
Is there a way to reference the result columns in a sum to get a total for the row without repeating the individal column queries?
Thanks,
Mark

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 1

Kenneth Wilhelmsson

SSC-Dedicated

Points: 30043

September 21, 2004 at 11:23 pm

#523903

Can you post an example of the query you have?

/Kenneth

Mark Hawkins Old Hand Points: 343 More actions · Answer 2

Select

(a.qty1*price1) as total1,

(a.qty2*price2) as total2,

(total1 + total2) as Gtotal

from a

Obviously I could do '((a.qty1*price1) + (a.qty2*price2)) as Gtotal' would work, but there must be a simpler way to do it.

Thanks,

Mark

AGS SSC Veteran Points: 239 More actions · Answer 3

how about something like this:

SELECT

(SUM(P0.Total1) + SUM(P0.Total2)) AS GTotal

FROM

(

SELECT

(A.qty1*price1) AS Total1,

(A.qty2*price2) AS Total2

FROM A

&nbsp P0

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 4

That is as simple as it gets

If you think about it (try to do the math yourself) - if you were to sum up the results of two other calculations, then you'd want to know what those calculations look like, right?

SQL Server has access to the values (ie the data) in the query, but the calculation lies in the query. It would be nice to be able to just reference the totals by name (total1 + total2), but that's not how it works (those aren't valid column names) - you have to specify the full expression you want for the grand total sum.

/Kenneth

Mark Hawkins Old Hand Points: 343 More actions · Answer 5

I thought there might be a way to reference expression columns. MS-Access can do it, and there's usualy a way to do it in sql if access can.

AGS I like your solution, but the total line is just part of a huge export query. I think in the end it will be easier just to repeat the formulas again for the grand total.

Thanks guys,

Mark

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 6

If nothing else, maintenance and debugging also becomes much easier when things are written explicitly.

/Kenneth

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 7

AGS gave you the right answer, I don't care how big your query is.

All the field names you use in the field list and other clauses (WHERE, ORDER BY, etc) must be defined in the FROM clause. Any column aliases you assign are not defined for use in other parts of the query. So to reuse names assigned to intermediate result columns the original query must become a subquery.

Just take your big query, put parenthesis around it and add an alias, then write the outer query using all the fields including the computed columns. This can go on for many levels.

Mark Hawkins Old Hand Points: 343 More actions · Answer 8

Scott,

Not that it is an issue here, as the query runs in the blink of an eye, but wouldn't a subquery run more slowly as it has to complete both processes?

Just for my education.

Thanks,

Mark

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 9

You may be thinking of correlated subqueries that cause extra I/O. This form of subquery is merely syntax, it lets you create computed fields and then refer to those fields in further calculations.

I've taken procedural code that looked roughly like this:

Read next record

compute (arbitrarily complex math)

compute

Update record

Loop until end of recordset

and changed it to this (HUGE speedup)

SELECT final field list

FROM (

SELECT intermediate fields 3

FROM (

SELECT intermediate fields 2

FROM (

SELECT intermediate fields 1

FROM original tables

) x

) y

) z

The execution plan shows the joins from the original query, but all the levels of subquery are collapsed into one "Compute Scalar" box which takes 0% of the query execution cost. If you think about it, you can do a lot of math on each row in a fraction of the time than it takes to read it from disk.

Mark Hawkins Old Hand Points: 343 More actions · Answer 10

Mark Hawkins

Old Hand

Points: 343

September 26, 2004 at 5:13 pm

#524440

Thanks,

Learn something new everyday.

Regards,

Mark

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 11

This is not a flame, but I have some quibbles with Joe's encyclopedic response

f) Nested query expressions follow the usual scoping rules you would expect from a block structured language like C, Pascal, Algol, etc. Namely, the innermost queries can reference columns and tables in the queries in which they are contained.

The innermost query can't refer to tables or columns from the queries in which it is contained, rather the outer queries can refer to columns defined by the inner queries. It's backwards from the usual scoping rules in block structured languages.

The ORDER BY clause cannot have expression in it

Sure it can. Very useful feature sometimes.

SELECT f(c2) AS c1, f(c1) AS c2 FROM Foobar

That is why such nonsense is illegal syntax

Confusing, yes; Illegal, no.

I'm referring to legal syntax in MS SQL Server, although there may be other environments that have different rules.

I once decided to buy Borland C++ because it was obviously better in terms of compliance with standard C++ than Microsoft. If I had the money, I would have bought a Beta VCR because it was obviously better than VHS. Eventually I learned to accept reality. My reality is I get paid to work with Microsoft systems, and I don't see anything in the budget for Oracle or DB2 any time soon. (MySQL is looking pretty inviting, though.)