Show unique group by

[vegasscorp]

I have sports database that I made for basketball. I need to display totals on one page from the database based on distinct gameid which is gid and distinct playerid which is called pid.

 

id | pid | gid | pts | thr | orb | drb | ast | etc.

1---10---1----19---2----2----3----3----etc.

2---5----1----9----0----1----1----1----etc.

3---7----1----5----1----0----2----6----etc.

4---10---2----12---1----2----4----2----etc.

 

example of database above

 

this is what I have but does not work.

 

<?php

mysql_select_db($database_schedule, $schedule);

$query_stattotals = "SELECT * FROM schedule2 GROUP BY 'pid' GROUP BY 'gid' ORDER BY gid ASC";

$stattotals = mysql_query($query_stattotals, $schedule) or die(mysql_error());

$row_stattotals = mysql_fetch_assoc($stattotals);

$totalRows_stattotals = mysql_num_rows($stattotals);

?>

 

I need to group by unique gid and pid. How is the code to be?

 

Thankyou very much for any help. kris

[BenInBlack]

here is your SQL statement: SELECT * FROM schedule2 GROUP BY 'pid' GROUP BY 'gid' ORDER BY gid ASC

 

when you do GROUP BY you only need it once so GROUP BY pid,gid

 

but wait! there's more...

when you use group by the only columns that can be displayed are the ones referenced in the group by, unless it is an agregate.

 

so you could do this

 

SELECT pid,gid,sum(pts) as pts, count(thr) as thr, avg(orb) FROM schedule2 GROUP BY pid,gid

 

reference

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html

[vegasscorp]

id | pid | gid | pts | thr | orb | drb | ast | etc.

1---10---1----19---2----2----3----3----etc.

2---5----1----9----0----1----1----1----etc.

3---7----1----5----1----0----2----6----etc.

4---10---2----12---1----2----4----2----etc.

 

example of database above

 

Basically I need to display the stats on one page by unique pid, and gid. I need to show totals for games played on just one line. I need too group by id, pid uniquely on that page. The code above is all I have.

[vegasscorp]

ID  gid  pid  Date        Opponent  Location  Ourscore  Oppscore  Results  Points  3pts  freethrows

34  1    3    2005-11-30  Whittier Cerritos    67              44        Win    14      9      5 

36  2    3    2005-11-30  Downey Cerritos    55              41        Win      8        3      5 

43  2    1    2005-11-30  Downey Cerritos    55              41        Win    16      6      10

 

this is my database so far Gid represents unique game and pid represents unique player.

 

I need to display each games totals on one page. this is the code Im trying and still shows all games.

I need it to extract from database to look like this.

 

Date        Opponent  Location  Ourscore  Oppscore  Results  Points  3pts  freethrows

2005-11-30  Whittier Cerritos    67              44        Win      14      9      5

2005-11-30  Downey Cerritos    55              41        Win      24      9      15

 

See how it display sum of gid 2

 

This is code I have.

 

<?php

mysql_select_db($database_schedule, $schedule);

$query_stats = "SELECT * FROM schedule2 GROUP BY gid,pid ORDER BY ID ASC";

$stats = mysql_query($query_stats, $schedule) or die(mysql_error());

$row_stats = mysql_fetch_assoc($stats);

$totalRows_stats = mysql_num_rows($stats);

?>

 

<td><div align="center"><?php echo $row_stats['Points']; ?></div></td>

<td><div align="center"><?php echo $row_stats['3pts']; ?></div></td>

<td><div align="center"><?php echo $row_stats['freethrows']; ?></div></td>

 

The code above is not totaling the Points  3pts  freethrows.

 

Thankyou for the help

[~n[EO]n~]

Try this

SELECT `Date`,`Opponent`,`Location`,`Ourscore`,`Results`,SUM(`Points`),SUM(`3pts`),SUM(`freethrows`) FROM schedule2 GROUP BY `gid`

[vegasscorp]

Frustrating. Thanks for reply but it needs to group by gid and pid. If it groups by gid its not adding the results for pid. Each line represents one game and one players results for one game. So your going to have many of the same gid numbers and pid for one game. I need to add results for unique gid and pid for one results page.

[clearstatcache]

select

  Date,

  Opponent,

  Location,

  Ourscore,

  Oppscore,

  Results,

  sum(Points) as Points,

  sum(3pts) as 3pts,

  sum(freethrows) as freethrows

from schedule2

group by gid

 

[vegasscorp]

I've already tried this which is same as above and still just groups by gid and NOT pid.

 

 

<?php

$query2 = "SELECT SUM(Points) from `schedule2` GROUP BY 'gid,pid' ";

 

$result18 = mysql_query($query2);

$row2 = mysql_fetch_array($result18);

 

echo "".(round($row2[0]))." ";

?>

 

I need it to sum Points by group gid and pid. This does not work. GROUP BY 'gid,pid'

 

Thankyou for any help