Sorting a SUM
 

Hello everyone,

Quite new to SQL, but trying to make some progress for a baseball-related site of mine.

For instance from my tables I want to produce a query that shows the career leaders for a given stat (Games Played in this case) for the team that I specify.

When I run this I get the info that I'm looking for: Position he played, First name, Last Name, Games he played for ATL (and it's obviously sorted by last name.

However, I want this data to be sorted by the sum (i.e. I want to know the top 25 for this stat) but when I try to GROUP BY or ORDER BY I do something wrong and get weird results.

What am I doing wrong and how could I limit the results to only the top 25?

Thanks in advance!

OldPedant would know for sure offhand, but I'm pretty sure MySQL allows you to order by an alias (I know I've tested this time and again but I can never remember which DBMS does what :P). So its very easy:
Looks like it would work. Ordering by SUM(G) should work as well (if you can't use an alias, but I'm pretty sure MySQL does).

Technically, you should *ALWAYS* make sure you GROUP BY *ALL* fields in the SELECT list that are not aggregate functions.

As Fou-Lu gave this:
If you had, for example, 3 different players with the last name of "JONES", then all three would be LUMPED TOGETHER because the GROUP BY specified *only* LName !!

Most database won't even allow the query as written there. MySQL does, but then it has funky rules about what using an incomplete GROUP BY means.

So...
Note how the SELECTed and GROUPed fields match.

By the by, you'd still be in trouble if you had two players who played the same position and had the same first and last names. Unsual, but not impossible.

p.s.: *ALL* DBs that I know of allow aliased column names in the ORDER BY clause. MySQL is unique in that it also allows them in the GROUP BY clause.

I've been doing *far* to much work in (sadly) Access O.o
Using the builders I kinda forget about the group by on all fields. Access for sure doesn't let you group by an alias.
*sigh* I wish I could put my scheduling in SQLServer.
I think oracle still rejects groupby on alias? Of course you can ways get around it by selecting a subquery, but its not quite the same. The newer ones may (I use 9g with veeerrrryyyy little actual manual queries; I wrote the sp's awhile ago, and none of them are aggregate).

Thanks, worked perfectly! Exactly what I was looking for.

One more question on this issue if I can be so bold..

So now that I have the top 25 I'm looking for, how can I go about labeling each row according to the ranking when it prints out the records.

I'm sure this is put in the loop somewhere, I just need a little help getting there.

So I would want it to look like:
1. PLAYER X, POS (Stat)
2. PLAYER Y, POS (Stat)
3. (...)

That's easiest done in the language you are working with. In PHP with the mysql library, that would look like so:
The $i is the important one there. You can format it as you see fit, probably using a table or some nice spans, but that's just a basic printf output.

You would just do that in PHP/ASP/JSP code.

In PHP, something like:
Or something like that. I don't use PHP, so I have to kind of wing it.

*********

EDIT: Too slow. But hey, I stuck my output into a nice <table> instead of into ugly unformatted rows. <grin/>

Where would the ++$i or ++$rownum go in the following output attempt? :D

Oh don't use the if/else like that. It looks like too much code. It can be much simpler. To answer your question, it would go in where you have RANKNUMBER. You can't use RANKNUMBER here since that would represent a constant in php (ie: unchangeable), and failing that it would become a string (which I hate that PHP does that). Effectively, its the $colour number, but instead you would keep going (I'll show you how to do that in one variable):
You should be able to apply your class to the table's tr css, and cascade it to the td from there.

Even better:
Why create that unneeded $class variable?

A critique, FouLu, with apologies.

It is true that works.

Because the test is actually done as and then, after the text, $i is incremented.

So when $i is even, that expression returns true. But because of the POST-INCREMENT, then by the time we get to the assignment to $class, indeed $i is odd.

So it works.

But it's clear as mud.

I *MUCH* prefer:
Because here it is clear we are FIRST bumping the value of $i and then testing that new value and, indeed, that new value agrees with the even/odd sense we get.

Of course, I really prefer it all as a ternary operation, as shown in my post, but even when not a ternary operator I think the PRE-INCREMENT makes the operation clearer.

Or do you not think so?

I also think it's a tiny bit clearer to do
because most people understand the concept of modulo better than bitwise ANDing. But I'll grant you that probably save 2 nanoseconds using the bitwise and.

To me, both of those are simply the same. It doesn't make a difference between the post or the pre operation on it. I see your point though, some people will read it easier seeing that $i is 1 and then checked versus $i is 0, checked and then incremented.

Even clearer though would be to simply not use the increment (either pre nor post) during the if or ternary, and apply it instead to the $i directly when displaying.
Ternary would be my approach as well; however, I typically limit the ternaries when I can while posting examples simply due to PHP's ltr evaluation of them. Its only a problem when nesting them, but it really is a pain:
Results in mouse, while using a language with rtl:
Results in (what I consider to be correct) 'cat'. I remember a thread not all that long ago for PHP that actually had an issue in the ternary of just a single level in the echo, which was caused by the concatenation and ternary together. But I can't quite recall what it was exactly.

I agree, certainly, that for clarity it's better to move the increment away from the even/odd test.

Though then I would do it like this:
that is, if you want row 1 to be row 1, make it so throughout the loop except for the point of incementing. I'd also be happy with:
I really don't like seeing stuff like
because now the test you made on $i earlier doesn't match the value displayed.

***********

That's really weird about PHP's nested ternary operations.

I can't even parse that to make sense and get "mouse" as the answer!!

That is, I can't see how to write that in, say, JS code and put parentheses into it in such a way to get "mouse".

In languages that don't use PHP's funky mysql_fetch_assoc stuff, I actually prefer using a for loop.

Could do it even with PHP/mysql_fetch_assoc:
No? Nobody ever said you *MUST* use the test of the incrementing variable as the second expression in the for did they?

Or, if that doesn't work (it should, but PHP is funny):
No?

And ONLY PHP would return a row if fetch_assoc can get one but false if it can't.

UGH UGH UGH.

Any self respecting language would return null instead of false, since an object reference being null makes sense, but a reference being false just cries of inconsistency.