MySQL Forums
Forum List  »  Newbie

Re: SUM problem.
Posted by: Jay Alverson
Date: March 15, 2009 07:14PM

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.

Options: ReplyQuote


Subject
Written By
Posted
March 12, 2009 03:29AM
March 12, 2009 09:52AM
March 13, 2009 07:10PM
March 13, 2009 11:10PM
March 14, 2009 04:01PM
March 14, 2009 06:40PM
March 15, 2009 07:54AM
March 15, 2009 10:41AM
March 15, 2009 10:35PM
March 16, 2009 09:33AM
March 16, 2009 09:52AM
March 16, 2009 02:46PM
March 16, 2009 04:15PM
March 17, 2009 02:11PM
March 17, 2009 05:03PM
March 17, 2009 06:31PM
March 17, 2009 10:17PM
March 18, 2009 06:03AM
March 15, 2009 05:41PM
Re: SUM problem.
March 15, 2009 07:14PM
March 16, 2009 08:34AM
March 18, 2009 12:52AM
March 21, 2009 05:58AM
March 21, 2009 11:13AM
March 22, 2009 05:01PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.