Professional Web Applications Themes

Group and Sum records - MySQL

Team,Score,Team,Score A,4,B,3 B,5,C,2 C,3,D,1 D,3,A,5 I have those 4 records in a database - I need to have them summarized like this: Team W L T Allowed Scored Differential A 2 0 0 6 9 +3 B 1 1 0 6 8 +2 C 1 1 0 6 5 -1 D 0 2 0 8 4 -4 Any ideas? Thanks!...

  1. #1

    Default Group and Sum records

    Team,Score,Team,Score
    A,4,B,3
    B,5,C,2
    C,3,D,1
    D,3,A,5

    I have those 4 records in a database - I need to have them summarized
    like this:

    Team W L T Allowed Scored Differential
    A 2 0 0 6 9 +3
    B 1 1 0 6 8 +2
    C 1 1 0 6 5 -1
    D 0 2 0 8 4 -4

    Any ideas?

    Thanks!

    garyricks@gmail.com Guest

  2. #2

    Default Group and Sum records

    Team1,Score1,Team2,Score2
    A,4,B,3
    B,5,C,2
    C,3,D,1
    D,3,A,5

    I have those 4 records in a database - I need to have them summarized
    like this:

    Team W L T Allowed Scored Differential
    A 2 0 0 6 9 +3
    B 1 1 0 6 8 +2
    C 1 1 0 6 5 -1
    D 0 2 0 8 4 -4

    Any ideas?

    Thanks!

    garyricks@gmail.com Guest

  3. #3

    Default Re: Group and Sum records

    [email]garyricks[/email] wrote:
    > Team,Score,Team,Score
    > A,4,B,3
    > B,5,C,2
    > C,3,D,1
    > D,3,A,5
    >
    > I have those 4 records in a database - I need to have them summarized
    > like this:
    >
    > Team W L T Allowed Scored Differential
    > A 2 0 0 6 9 +3
    > B 1 1 0 6 8 +2
    > C 1 1 0 6 5 -1
    > D 0 2 0 8 4 -4
    >
    > Any ideas?
    >
    > Thanks!
    Where does the "Allowed" data come from?


    Paul Lautman Guest

  4. #4

    Default Re: Group and Sum records

    Allowed comes from the other teams score -

    Team A for example

    Game 1 They scored 4 and allowed 3
    Game 4 They scored 5 and allowed 3

    I have tried many ways to get this to work - can't seem to find a way
    to do it.

    Thanks.

    Paul Lautman wrote:
    > [email]garyricks[/email] wrote:
    > > Team,Score,Team,Score
    > > A,4,B,3
    > > B,5,C,2
    > > C,3,D,1
    > > D,3,A,5
    > >
    > > I have those 4 records in a database - I need to have them summarized
    > > like this:
    > >
    > > Team W L T Allowed Scored Differential
    > > A 2 0 0 6 9 +3
    > > B 1 1 0 6 8 +2
    > > C 1 1 0 6 5 -1
    > > D 0 2 0 8 4 -4
    > >
    > > Any ideas?
    > >
    > > Thanks!
    >
    > Where does the "Allowed" data come from?
    garyricks@gmail.com Guest

  5. #5

    Default Re: Group and Sum records


    <garyricks> schreef in bericht
    news:1150549359.395743.276610u72g2000cwu.googlegr oups.com...
    > Team,Score,Team,Score
    > A,4,B,3
    > B,5,C,2
    > C,3,D,1
    > D,3,A,5
    >
    > I have those 4 records in a database - I need to have them summarized
    > like this:
    >
    > Team W L T Allowed Scored Differential
    > A 2 0 0 6 9 +3
    > B 1 1 0 6 8 +2
    > C 1 1 0 6 5 -1
    > D 0 2 0 8 4 -4
    >
    > Any ideas?
    >
    > Thanks!
    >
    When your first table is defined like:
    CREATE TABLE `score` (
    `team1` char(1) NOT NULL default '',
    `score1` int(10) unsigned NOT NULL default '0',
    `team2` char(1) NOT NULL default '',
    `score2` int(10) unsigned NOT NULL default '0',
    PRIMARY KEY (`team1`,`score1`,`team2`,`score2`)
    )
    -- Pasted from phpMyAdmin.... ;-)

    You can do the following INSERT
    INSERT into score SELECT team2, score2, team1, score1 from score

    After this you will have 8 rows in the table score, and the following will
    give your result:

    select Team1,
    sum(if(score1>score2,1,0)) W,
    sum(if(score1<score2,1,0)) L,
    sum(if(score1=score2,1,0)) T,
    sum(score2) Allowed,
    sum(score1) Scored,
    sum(score1)-sum(score2) Diff
    from score group by team1


    Luuk Guest

Similar Threads

  1. "group by" - order of rows in group
    By aljosa.mohorovic@gmail.com in forum MySQL
    Replies: 1
    Last Post: September 1st, 08:50 AM
  2. May 29 Sydney Developers Group study group
    By 105 in forum Macromedia Flex General Discussion
    Replies: 0
    Last Post: May 25th, 02:56 PM
  3. cfgrid inside a <cfoutput query="myQuery" group="GROUP">
    By DavidGhous in forum Coldfusion Flash Integration
    Replies: 1
    Last Post: April 12th, 07:23 PM
  4. ASP Group Same Records and Give Total Count
    By gotcha in forum ASP Database
    Replies: 1
    Last Post: August 6th, 08:50 PM
  5. group
    By Dave in forum PERL Beginners
    Replies: 0
    Last Post: December 25th, 07:05 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139