How do I sum results columns (grand total) without repeating all the formulas
-
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
-
Can you post an example of the query you have?
/Kenneth
-
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
-
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
 
P0 -
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
-
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
-
If nothing else, maintenance and debugging also becomes much easier when things are written explicitly.
/Kenneth
-
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.
-
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
-
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
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.
-
Thanks,
Learn something new everyday.
Regards,
Mark
-
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.)
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply