Re: SUM problem.
Is this better ?
There's some trouble with tie scores... which can be solved in the last part
of the query... showing 9 and 18 scores, instead of 8.
mysql>
mysql> select version();
+---------------------+
| version() |
+---------------------+
| 5.0.67-community-nt |
+---------------------+
1 row in set (0.00 sec)
mysql>
mysql> use test;
Database changed
mysql>
mysql> drop table if exists Bowler;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> create table Bowler (
-> id smallint,
-> name varchar(50),
-> team smallint,
-> total_pins smallint,
-> game1 smallint,
-> game2 smallint,
-> game3 smallint,
-> average float (5,2));
Query OK, 0 rows affected (0.08 sec)
mysql>
mysql> insert into Bowler values
-> (1,"Willy Moresby",35,767,248,241,278,255.67),
-> (2,"Ramsey Gaines",22,727,223,224,280,242.33),
-> (3,"Mark Vaugh",101,709,232,233,244,236.33),
-> (4,"Kelly Priest",174,705,233,233,239,235.00),
-> (5,"Jeff Pells",204,704,282,228,194,234.67),
-> (6,"Roger Attwell",55,685,272,209,204,228.33),
-> (7,"Miles Berry",43,685,188,204,293,228.33),
-> (8,"Grant Thirsby",155,683,234,241,208,227.67),
-> (9,"Hugo Mensaki",161,680,274,203,203,226.67),
-> (10,"Jim Dallid",172,677,227,181,269,225.67),
-> (11,"Ken Lagna",131,673,243,252,178,224.33),
-> (12,"Jerry Huffwell",92,673,234,238,201,224.33),
-> (13,"Andy Larson",54,673,238,182,253,224.33),
-> (14,"Walter Retron",45,671,241,204,226,223.67),
-> (15,"Moe Maines",15,668,242,223,203,222.67),
-> (16,"Dave Letugo",151,667,234,220,213,222.33),
-> (17,"Lorel Kaneer",12,665,238,183,244,221.67),
-> (18,"Tony Uppus",61,664,204,220,240,221.33),
-> (19,"Silvio Jurano",31,660,255,206,199,220.00),
-> (20,"Noley Machsur",123,659,260,206,193,219.67);
Query OK, 20 rows affected (0.02 sec)
Records: 20 Duplicates: 0 Warnings: 0
mysql>
mysql> select * from Bowler;
+------+----------------+------+------------+-------+-------+-------+---------+
| id | name | team | total_pins | game1 | game2 | game3 | average |
+------+----------------+------+------------+-------+-------+-------+---------+
| 1 | Willy Moresby | 35 | 767 | 248 | 241 | 278 | 255.67 |
| 2 | Ramsey Gaines | 22 | 727 | 223 | 224 | 280 | 242.33 |
| 3 | Mark Vaugh | 101 | 709 | 232 | 233 | 244 | 236.33 |
| 4 | Kelly Priest | 174 | 705 | 233 | 233 | 239 | 235.00 |
| 5 | Jeff Pells | 204 | 704 | 282 | 228 | 194 | 234.67 |
| 6 | Roger Attwell | 55 | 685 | 272 | 209 | 204 | 228.33 |
| 7 | Miles Berry | 43 | 685 | 188 | 204 | 293 | 228.33 |
| 8 | Grant Thirsby | 155 | 683 | 234 | 241 | 208 | 227.67 |
| 9 | Hugo Mensaki | 161 | 680 | 274 | 203 | 203 | 226.67 |
| 10 | Jim Dallid | 172 | 677 | 227 | 181 | 269 | 225.67 |
| 11 | Ken Lagna | 131 | 673 | 243 | 252 | 178 | 224.33 |
| 12 | Jerry Huffwell | 92 | 673 | 234 | 238 | 201 | 224.33 |
| 13 | Andy Larson | 54 | 673 | 238 | 182 | 253 | 224.33 |
| 14 | Walter Retron | 45 | 671 | 241 | 204 | 226 | 223.67 |
| 15 | Moe Maines | 15 | 668 | 242 | 223 | 203 | 222.67 |
| 16 | Dave Letugo | 151 | 667 | 234 | 220 | 213 | 222.33 |
| 17 | Lorel Kaneer | 12 | 665 | 238 | 183 | 244 | 221.67 |
| 18 | Tony Uppus | 61 | 664 | 204 | 220 | 240 | 221.33 |
| 19 | Silvio Jurano | 31 | 660 | 255 | 206 | 199 | 220.00 |
| 20 | Noley Machsur | 123 | 659 | 260 | 206 | 193 | 219.67 |
+------+----------------+------+------------+-------+-------+-------+---------+
20 rows in set (0.00 sec)
mysql>
mysql> # top 8 for each game...
mysql> select id, name, game1 from Bowler order by game1 desc limit 8;
+------+---------------+-------+
| id | name | game1 |
+------+---------------+-------+
| 5 | Jeff Pells | 282 |
| 9 | Hugo Mensaki | 274 |
| 6 | Roger Attwell | 272 |
| 20 | Noley Machsur | 260 |
| 19 | Silvio Jurano | 255 |
| 1 | Willy Moresby | 248 |
| 11 | Ken Lagna | 243 |
| 15 | Moe Maines | 242 |
+------+---------------+-------+
8 rows in set (0.00 sec)
mysql> select id, name, game2 from Bowler order by game2 desc limit 8;
+------+----------------+-------+
| id | name | game2 |
+------+----------------+-------+
| 11 | Ken Lagna | 252 |
| 1 | Willy Moresby | 241 |
| 8 | Grant Thirsby | 241 |
| 12 | Jerry Huffwell | 238 |
| 4 | Kelly Priest | 233 |
| 3 | Mark Vaugh | 233 |
| 5 | Jeff Pells | 228 |
| 2 | Ramsey Gaines | 224 |
+------+----------------+-------+
8 rows in set (0.00 sec)
mysql> select id, name, game3 from Bowler order by game3 desc limit 8;
+------+---------------+-------+
| id | name | game3 |
+------+---------------+-------+
| 7 | Miles Berry | 293 |
| 2 | Ramsey Gaines | 280 |
| 1 | Willy Moresby | 278 |
| 10 | Jim Dallid | 269 |
| 13 | Andy Larson | 253 |
| 3 | Mark Vaugh | 244 |
| 17 | Lorel Kaneer | 244 |
| 18 | Tony Uppus | 240 |
+------+---------------+-------+
8 rows in set (0.00 sec)
mysql>
mysql> select game1 into @game1 from Bowler order by game1 desc limit 7,1;
Query OK, 1 row affected (0.00 sec)
mysql> select @game1;
+--------+
| @game1 |
+--------+
| 242 |
+--------+
1 row in set (0.00 sec)
mysql> select count(*), sum(game1) as "Top 8 Total Pins", sum(game1)/8.00 as "Average" from Bowler where game1 >= @game1;
+----------+------------------+----------+
| count(*) | Top 8 Total Pins | Average |
+----------+------------------+----------+
| 8 | 2076 | 259.5000 |
+----------+------------------+----------+
1 row in set (0.02 sec)
mysql>
mysql> #---------------------------------------------
mysql> # Ooooooppps !!!
mysql> #---------------------------------------------
mysql> select game2 into @game2 from Bowler order by game1 desc limit 7,1;
Query OK, 1 row affected (0.00 sec)
mysql> select @game2;
+--------+
| @game2 |
+--------+
| 223 |
+--------+
1 row in set (0.00 sec)
mysql> select count(*), sum(game2) as "Top 8 Total Pins", sum(game2)/8.00 as "Average" from Bowler where game1 >= @game2;
+----------+------------------+----------+
| count(*) | Top 8 Total Pins | Average |
+----------+------------------+----------+
| 18 | 3907 | 488.3750 |
+----------+------------------+----------+
1 row in set (0.00 sec)
mysql>
mysql> #---------------------------------------------
mysql> # Ooooooppps !!!
mysql> #---------------------------------------------
mysql> select game3 into @game3 from Bowler order by game3 desc limit 7,1;
Query OK, 1 row affected (0.00 sec)
mysql> select @game3;
+--------+
| @game3 |
+--------+
| 240 |
+--------+
1 row in set (0.00 sec)
mysql> select count(*), sum(game3) as "Top 8 Total Pins", sum(game3)/8.00 as "Average" from Bowler where game1 >= @game3;
+----------+------------------+----------+
| count(*) | Top 8 Total Pins | Average |
+----------+------------------+----------+
| 9 | 1878 | 234.7500 |
+----------+------------------+----------+
1 row in set (0.00 sec)
mysql>
mysql> set @score_count := 0;
Query OK, 0 rows affected (0.00 sec)
mysql> set @total_pins := 0;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> select name, game1,
-> if(@score_count < 8, @total_pins:=@total_pins+game1, "") as "Total Pins",
-> (@score_count:=@score_count + 1) as "Score Count"
-> from Bowler order by game1 desc;
+----------------+-------+------------+-------------+
| name | game1 | Total Pins | Score Count |
+----------------+-------+------------+-------------+
| Jeff Pells | 282 | 282 | 1 |
| Hugo Mensaki | 274 | 556 | 2 |
| Roger Attwell | 272 | 828 | 3 |
| Noley Machsur | 260 | 1088 | 4 |
| Silvio Jurano | 255 | 1343 | 5 |
| Willy Moresby | 248 | 1591 | 6 |
| Ken Lagna | 243 | 1834 | 7 |
| Moe Maines | 242 | 2076 | 8 |
| Walter Retron | 241 | | 9 |
| Andy Larson | 238 | | 10 |
| Lorel Kaneer | 238 | | 11 |
| Grant Thirsby | 234 | | 12 |
| Jerry Huffwell | 234 | | 13 |
| Dave Letugo | 234 | | 14 |
| Kelly Priest | 233 | | 15 |
| Mark Vaugh | 232 | | 16 |
| Jim Dallid | 227 | | 17 |
| Ramsey Gaines | 223 | | 18 |
| Tony Uppus | 204 | | 19 |
| Miles Berry | 188 | | 20 |
+----------------+-------+------------+-------------+
20 rows in set (0.00 sec)
mysql>
mysql> select @score_count, @total_pins;
+--------------+-------------+
| @score_count | @total_pins |
+--------------+-------------+
| 20 | 2076 |
+--------------+-------------+
1 row in set (0.00 sec)
mysql>
mysql> set @score_count := 0;
Query OK, 0 rows affected (0.00 sec)
mysql> set @total_pins := 0;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> select name, game2,
-> if(@score_count < 8, @total_pins:=@total_pins+game2, "") as "Total Pins",
-> (@score_count:=@score_count + 1) as "Score Count"
-> from Bowler order by game2 desc;
+----------------+-------+------------+-------------+
| name | game2 | Total Pins | Score Count |
+----------------+-------+------------+-------------+
| Ken Lagna | 252 | 252 | 1 |
| Willy Moresby | 241 | 493 | 2 |
| Grant Thirsby | 241 | 734 | 3 |
| Jerry Huffwell | 238 | 972 | 4 |
| Kelly Priest | 233 | 1205 | 5 |
| Mark Vaugh | 233 | 1438 | 6 |
| Jeff Pells | 228 | 1666 | 7 |
| Ramsey Gaines | 224 | 1890 | 8 |
| Moe Maines | 223 | | 9 |
| Tony Uppus | 220 | | 10 |
| Dave Letugo | 220 | | 11 |
| Roger Attwell | 209 | | 12 |
| Noley Machsur | 206 | | 13 |
| Silvio Jurano | 206 | | 14 |
| Walter Retron | 204 | | 15 |
| Miles Berry | 204 | | 16 |
| Hugo Mensaki | 203 | | 17 |
| Lorel Kaneer | 183 | | 18 |
| Andy Larson | 182 | | 19 |
| Jim Dallid | 181 | | 20 |
+----------------+-------+------------+-------------+
20 rows in set (0.00 sec)
mysql>
mysql> select @score_count, @total_pins;
+--------------+-------------+
| @score_count | @total_pins |
+--------------+-------------+
| 20 | 1890 |
+--------------+-------------+
1 row in set (0.00 sec)
mysql>
mysql> set @score_count := 0;
Query OK, 0 rows affected (0.00 sec)
mysql> set @total_pins := 0;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> select name, game3,
-> if(@score_count < 8, @total_pins:=@total_pins+game3, "") as "Total Pins",
-> (@score_count:=@score_count + 1) as "Score Count"
-> from Bowler order by game3 desc;
+----------------+-------+------------+-------------+
| name | game3 | Total Pins | Score Count |
+----------------+-------+------------+-------------+
| Miles Berry | 293 | 293 | 1 |
| Ramsey Gaines | 280 | 573 | 2 |
| Willy Moresby | 278 | 851 | 3 |
| Jim Dallid | 269 | 1120 | 4 |
| Andy Larson | 253 | 1373 | 5 |
| Mark Vaugh | 244 | 1617 | 6 |
| Lorel Kaneer | 244 | 1861 | 7 |
| Tony Uppus | 240 | 2101 | 8 |
| Kelly Priest | 239 | | 9 |
| Walter Retron | 226 | | 10 |
| Dave Letugo | 213 | | 11 |
| Grant Thirsby | 208 | | 12 |
| Roger Attwell | 204 | | 13 |
| Hugo Mensaki | 203 | | 14 |
| Moe Maines | 203 | | 15 |
| Jerry Huffwell | 201 | | 16 |
| Silvio Jurano | 199 | | 17 |
| Jeff Pells | 194 | | 18 |
| Noley Machsur | 193 | | 19 |
| Ken Lagna | 178 | | 20 |
+----------------+-------+------------+-------------+
20 rows in set (0.00 sec)
mysql>
mysql> select @score_count, @total_pins;
+--------------+-------------+
| @score_count | @total_pins |
+--------------+-------------+
| 20 | 2101 |
+--------------+-------------+
1 row in set (0.00 sec)
mysql>
mysql> #---------------------------------------------
mysql> # Pretty version using a Derived Table...
mysql> #---------------------------------------------
mysql>
mysql> set @score_count := 0;
Query OK, 0 rows affected (0.02 sec)
mysql> set @total_pins := 0;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> select name, game2 as "Game 2", tp as "Total Pins" from (
-> select name, game2,
-> if(@score_count < 8, @total_pins:=@total_pins+game2, "") as tp,
-> (@score_count:=@score_count + 1) as "Score Count"
-> from Bowler order by game2 desc) as ScratchTable where tp <> "";
+----------------+--------+------------+
| name | Game 2 | Total Pins |
+----------------+--------+------------+
| Ken Lagna | 252 | 252 |
| Willy Moresby | 241 | 493 |
| Grant Thirsby | 241 | 734 |
| Jerry Huffwell | 238 | 972 |
| Kelly Priest | 233 | 1205 |
| Mark Vaugh | 233 | 1438 |
| Jeff Pells | 228 | 1666 |
| Ramsey Gaines | 224 | 1890 |
+----------------+--------+------------+
8 rows in set (0.00 sec)
mysql>
mysql>
mysql> notee
This way I can show individual scores + show a sum of the Top 8 scores in a single display (I realize you may not want that, but it's easily changed).
>
Thanks, Jay
Edited 1 time(s). Last edit at 03/15/2009 07:17PM by Jay Alverson.