# Q: Sorting by the sum of columns? - MySQL

Hi Folks! I'm a newbie MySQL programmer. While doing my project I have faced following problem: I have five columns in my table (indexed 'id' and four with numerical values). I would like to sum the numerical values into a kind of virtual column and sort the table accordingly to the decreasing values of the sum (of the virual column). I have searched in numerous tutorials and FAQs but have not found description of even similar situation. Please help Best regards, Genz...

1. ## Q: Sorting by the sum of columns?

Hi Folks!

I'm a newbie MySQL programmer. While doing my project I have faced
following problem:

I have five columns in my table (indexed 'id' and four with numerical
values). I would like to sum the numerical values into a kind of
virtual column and sort the table accordingly to the decreasing values
of the sum (of the virual column).

I have searched in numerous tutorials and FAQs but have not found
description of even similar situation.

Best regards,

Genz

genzeryk Guest

2. ## Re: Q: Sorting by the sum of columns?

On May 12, 3:44 pm, genzeryk <com> wrote:

select id, (a + b + c + d) theSum
from someTable
order by (a + b + c + d) desc

ZeldorBlat Guest

3. ## Re: Q: Sorting by the sum of columns?

ZeldorBlat wrote:
>
> select id, (a + b + c + d) theSum
> from someTable
> order by (a + b + c + d) desc[/ref]

That seems needlessly wasted on resources, by summing the columns twice.
Surely either of these 2 would be better:

SELECT
`id`,
(`a` + `b` + `c` + `d`) `theSum`
FROM `someTable`
ORDER BY `theSum`

or

SELECT
`id`,
(`a` + `b` + `c` + `d`) `theSum`
FROM `someTable`
ORDER BY 2

Paul Guest

4. ## Re: Q: Sorting by the sum of columns?

On May 12, 3:53 pm, "Paul Lautman" <com>
wrote: [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]

>
> That seems needlessly wasted on resources, by summing the columns twice.
> Surely either of these 2 would be better:
>
> SELECT
> `id`,
> (`a` + `b` + `c` + `d`) `theSum`
> FROM `someTable`
> ORDER BY `theSum`
>
> or
>
> SELECT
> `id`,
> (`a` + `b` + `c` + `d`) `theSum`
> FROM `someTable`
> ORDER BY 2[/ref]

Do you know for a fact that MySQL will internally calculate the sums
twice? If the database engine is properly optimized I would guess
that it won't.

ZeldorBlat Guest

5. ## Re: Q: Sorting by the sum of columns?

ZeldorBlat wrote:
>>
>>
>>
>>
>>
>>
>>
>>
>> That seems needlessly wasted on resources, by summing the columns
>> twice. Surely either of these 2 would be better:
>>
>> SELECT
>> `id`,
>> (`a` + `b` + `c` + `d`) `theSum`
>> FROM `someTable`
>> ORDER BY `theSum`
>>
>> or
>>
>> SELECT
>> `id`,
>> (`a` + `b` + `c` + `d`) `theSum`
>> FROM `someTable`
>> ORDER BY 2[/ref]
>
> Do you know for a fact that MySQL will internally calculate the sums
> twice? If the database engine is properly optimized I would guess
> that it won't.[/ref]

Yes I do know that for a fact. No matter how well the database engine is
likely to be optimised, it is not going to bother tryjng to recognise that
two distinct pieces of calculation happen to have the same fields. That is
why you are able to refer to previously calculated fields. Optimisers assume
some level of intelligence from the programmer.

Paul Guest

6. ## Re: Q: Sorting by the sum of columns?

On May 13, 9:42 am, "Paul Lautman" <com>
wrote: [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]

>
> Yes I do know that for a fact. No matter how well the database engine is
> likely to be optimised, it is not going to bother tryjng to recognise that
> two distinct pieces of calculation happen to have the same fields. That is
> why you are able to refer to previously calculated fields. Optimisers assume
> some level of intelligence from the programmer.[/ref]

I was unable to find that doented anywhere. Do you have a source?
I'm curious now...

ZeldorBlat Guest

7. ## Re: Q: Sorting by the sum of columns?

On 13 May, 22:41, ZeldorBlat <com> wrote: [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]

>
> I was unable to find that doented anywhere. Do you have a source?
> I'm curious now...- Hide quoted text -
>
> - Show quoted text -[/ref]

Sorry for the delay in answering this one. My source is the manual.
On the page http://dev.mysql.com/doc/refman/5.0/en/select.html
Where it says:

"Columns selected for output can be referred to in ORDER BY and GROUP
BY clauses using column names, column aliases, or column positions."

And of course it defines an Order By statement as:

ORDER BY {col_name | expr | position}

It does not say that columns selected for output can be referred to by
using an expression that happens to match the expression that created
the data for that column. Thus, unless you use one of the methods to
refer to the output column, you are left with an expression that will
have to be evaluated.

Captain Guest

8. ## Re: Q: Sorting by the sum of columns?

On May 15, 6:57 am, Captain Paralytic <com> wrote:
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]

>
> Sorry for the delay in answering this one. My source is the manual.
> On the pagehttp://dev.mysql.com/doc/refman/5.0/en/select.html
> Where it says:
>
> "Columns selected for output can be referred to in ORDER BY and GROUP
> BY clauses using column names, column aliases, or column positions."
>
> And of course it defines an Order By statement as:
>
> ORDER BY {col_name | expr | position}
>
> It does not say that columns selected for output can be referred to by
> using an expression that happens to match the expression that created
> the data for that column. Thus, unless you use one of the methods to
> refer to the output column, you are left with an expression that will
> have to be evaluated.[/ref]

Ok, but nowhere does that say that the expression will or will not be
internally evaluated a second time. If the database is smart enough
to recognize when you have an expression in the group by clause that
is not in the select list (in which case it complains), I would expect
that it is smart enough to match an expression in the select list to
an expression in the order by clause.

ZeldorBlat Guest

9. ## Re: Q: Sorting by the sum of columns?

ZeldorBlat wrote:
>
> Ok, but nowhere does that say that the expression will or will not be
> internally evaluated a second time. If the database is smart enough
> to recognize when you have an expression in the group by clause that
> is not in the select list (in which case it complains), I would expect
> that it is smart enough to match an expression in the select list to
> an expression in the order by clause.
>
>[/ref]
It's barely worth discussing. As focused as database companies are on
performance I doubt any would re-evaluate any expression that lexically
matches another either before or after compilation.

Referring to column aliases is a syntactic feature, not a performance
feature. It's a good thing that MySQL has column aliases. I remember
that being one of my favorite features of Teradata's SQL.

Regardless, the answer to the question is unlikely to be found in the
manuals. To know for sure the answer would either have to come from a
MySQL engineer or someone else familiar with MySQL's pr, compiler,
evaluator, or looking through the code.

However, as confident as Mr. Lautman is of the "facts," there are none
supporting the /assumption/ the ORDER BY clause' re-evaluates duplicate
expressions. Though I have no "facts" supporting my assumptions, I'm
equally confident that considering SQL databases maturity, the amount of
engineering given them, the papers published on optimization strategies,
the competition for paying licensees, and the constant pressure to
improve performance, that such an issue as duplicate expression
evaluation is unlikely to have gone unnoticed for 30+ years.

--
Visit <http://blogs.instreamfinancial.com/anything.php>
to read my rants on technology and the finance industry.
Thomas Guest

10. ## Re: Q: Sorting by the sum of columns?

Thomas Gagne wrote:
>>
>> Ok, but nowhere does that say that the expression will or will not be
>> internally evaluated a second time. If the database is smart enough
>> to recognize when you have an expression in the group by clause that
>> is not in the select list (in which case it complains), I would
>> expect that it is smart enough to match an expression in the select
>> list to an expression in the order by clause.
>>
>>[/ref]
> It's barely worth discussing. As focused as database companies are on
> performance I doubt any would re-evaluate any expression that
> lexically matches another either before or after compilation.
>
> Referring to column aliases is a syntactic feature, not a performance
> feature. It's a good thing that MySQL has column aliases. I remember
> that being one of my favorite features of Teradata's SQL.
>
> Regardless, the answer to the question is unlikely to be found in the
> manuals. To know for sure the answer would either have to come from a
> MySQL engineer or someone else familiar with MySQL's pr, compiler,
> evaluator, or looking through the code.
>
> However, as confident as Mr. Lautman is of the "facts," there are none
> supporting the /assumption/ the ORDER BY clause' re-evaluates
> duplicate expressions. Though I have no "facts" supporting my
> assumptions, I'm equally confident that considering SQL databases
> maturity, the amount of engineering given them, the papers published
> on optimization strategies, the competition for paying licensees, and
> the constant pressure to improve performance, that such an issue as
> duplicate expression evaluation is unlikely to have gone unnoticed
> for 30+ years.[/ref]

The manual tells you when an output column will be referenced. If it would
be referenced in all cases then the writers would not have bothered to list
the only 2 that applied. The manual doesn't tell you everything that does
not apply. For instance it tells you that the SELECT syntax starts with
SELECT. It doesn't tell you that you cannot spell SELECT as SELETC or any
other way.

ZeldorBlat's mention of the GROUP BY clause is totally unrelated and in fact
wrong. It is indeed perfectly possible to have an expression in the GROUP BY
clause that is not in the SELECT list. And it does not "complain" as he
states. Instead it works! I just tried it.

Regardless of what you say about optimisation strategies, the developers of
databases do, as I have said before, assume a bit of intelligence in the
people who use them. They have stated quite clearly in the manual when data
in a column can be referenced. The would not bother, no matter how many
papers had been written to try to optimise a query for something like this
when they have already given and doented the 2 correct ways to do it.

Paul Guest

11. ## Re: Q: Sorting by the sum of columns?

Paul Lautman wrote:
Have you used other SQL products, or is MySQL the first? How did
grouping by expressions not in the select list work then?
You might assume so, but for many SQL servers optimization strategies
routinely dispense with table and join order, and instead rely on
indexes and statistics to decide the best query plan. I'm not a MySQL
which for SQL Server and Sybase basically neuters the optimizer, tells
it to stay home, and says, "I'm the programmer, do it the way I've
written it."

In many instances, the optimizer can be smarter than the programmer.
And based on what I've seen from many programmers, that's not that hard
to do. In my early days I'm pretty sure it was smarter than me. Maybe
it still is...
The two correct ways to express syntax. Not ways to force or avoid
redundant expression evaluation.

Perhaps a SHOWPLAN might help resolve this?

--
Visit <http://blogs.instreamfinancial.com/anything.php>
to read my rants on technology and the finance industry.
Thomas Guest

12. ## Re: Q: Sorting by the sum of columns?

On 16 May, 04:47, Thomas Gagne <com> wrote:

>[/ref]
| Have you used other SQL products, or is MySQL the first? How did
| grouping by expressions not in the select list work then?

I have used others, in particular DB/2. However, this discussion is
The manual that I was refering to was the MySQL manual. I would not
make statements about another database system without having checked
the manuals for them. The extensions to SQL standard regarding "GROUP
BY" that have been mentioned above may not be valid for other systems.

>
> You might assume so, but for many SQL servers optimization strategies
> routinely dispense with table and join order, and instead rely on
> indexes and statistics to decide the best query plan. I'm not a MySQL
> expert, but perhaps you've tried using (or have read about) "forceplan,"
> which for SQL Server and Sybase basically neuters the optimizer, tells
> it to stay home, and says, "I'm the programmer, do it the way I've
> written it."[/ref]
But this discussion is about MySQL. It is the MySQL Manual that I was
looking at.

Maybe some of them are. But the MySQL manual is quite clear on when
column data is referred to and it is MySQL we are discussing here.

>
> The two correct ways to express syntax. Not ways to force or avoid
> redundant expression evaluation.
>
> Perhaps a SHOWPLAN might help resolve this?
>
> --
> Visit <http://blogs.instreamfinancial.com/anything.php>
> to read my rants on technology and the finance industry.[/ref]

Captain Guest

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•