SELECT count / group by question

[Anon]

am doing a query to pull out data from one of my tables, using this select

SELECT match_info.season_id, count(match_info.season_id) as count, sum(match_info.for) as count1, sum(match_info.against) as count2, global_season.season_name from match_info, global_season WHERE match_info.season_id = global_season.id AND match_info.match_date <= curdate() GROUP BY match_info.season_id ORDER BY match_info.season_id

This works fine, pulls out the season id and then then the total amount of matches for the season, the goals scored (for) and the goals against (against) and also the season name (its a football styled website )

The data looks something like

2 1 2 1 00/01
1 12 43 25 01/02

In the table, I have 13 records ... each record has a field (called RES) that stores either a W a D or a L

It only can have one of those values and basically I'd like my query to show three extra columns

2 4 2 1 00/01
plus 3 2 1

The new values are 3 = Number of rows with a W in, 2 = number of rows with a D in and finally 1 = number of rows with a L in

Any ideas?

[tasistro]

Ok I have an accounting program that does something like the thing you want say a want to select all operations and group them by account, sub account and concept type.

SELECT %account field%, %subaccount field%, %concept field%, sum(%money_field%) FROM %operations table% GROUP BY %account field%, %sub account field%, %concept field% ORDER BY (whatever criteria)

This returns the amount of money separated by all groups. In your case you could add in the SELECT space

SELECT %all your other code%, RES, count(RES) FROM %all your from code% WHERE
%all your where code% GROUP BY match_info.season_id, RES ORDER BY match_info.season_id, %possibly RES too%

RES is to be expanded to the real field name.

Saludos
Gerardo

[Anon]

Thanks, technically what you've done there is what I had experimented with ... only it doesnt appear in the way I want

SELECT match_info.season_id, match_info.res, count(match_info.season_id) as count, sum(match_info.for) as count1, sum(match_info.against) as count2, global_season.season_name from match_info, global_season WHERE match_info.season_id = global_season.id AND match_info.match_date <= curdate() GROUP BY match_info.season_id, match_info.res ORDER BY match_info.season_id DESC

Is the newest query (based on your ideas)

The problem is that I want a row for each seasons data, so season 01/02 will show 1 value of each of FOR, AGAINST, WON, DRAWN, LOST

The way it appears now is that each season has three entries, because I am obviously grouping by RES.

I am trying to present this in the following format

	while ($section = mysql_fetch_array($result))
	{
	echo "<tr>\n";
	echo "<td valign='middle'  width='270' bgcolor='$BEIGE2'>\n";
	echo "<font class='main'><b><A HREF=\"match_info.php?act=seasonfixtures&seasonid=$section[season_id]\">Season $section[season_name]</A></b></font>\n";
	echo "</td>\n";

	echo "<td valign='middle' align='center' align='center' width='35' bgcolor='$BEIGE2'>\n";
	echo "<font class='main'><b>$section[count]</b></font>\n";
	echo "</td>\n";

	echo "<td valign='middle' align='center' align='center' width='35' bgcolor='$BEIGE2'>\n";
	echo "<font class='main'><b>$section[count1]</b></font>\n";
	echo "</td>\n";

	echo "<td valign='middle' align='center' align='center' width='35' bgcolor='$BEIGE2'>\n";
	echo "<font class='main'><b>$section[count2]</b></font>\n";
	echo "</td>\n";
	echo "</tr>\n"; 	
	}

And want to add some new columns for W, D, L

[tasistro]

Think I've got it. With this sql I managed what I think you want.

SQL:

select season, (@ws:="W"),(@ds:="D"),(@ls:="L"),
sum(IF(res=@ws,1,res)) as W , sum(IF(res=@ds,1,res)) as D, sum(IF(res=@ls,1,res)) as L
from md group by season;

RESULT

+--------+------------+------------+------------+------+------+------+
| season | (@ws:="W") | (@ds:="D") | (@ls:="L") | W | D | L |
+--------+------------+------------+------------+------+------+------+
| 1 | W | D | L | 2 | 1 | 1 |
| 2 | W | D | L | 3 | 1 | 2 |
+--------+------------+------------+------------+------+------+------+

The table definiton and data follows

phpMyAdmin MySQL-Dump

version 2.2.0

http://phpwizard.net/phpMyAdmin/

http://phpmyadmin.sourceforge.net/ (download page)

#

Host: localhost

Generation Time: November 12, 2001, 7:51 pm

Server version: 3.23.36

PHP Version: 4.0.4pl1

Database : phpbuilder

--------------------------------------------------------

Table structure for table md

CREATE TABLE md (

ID int(11) NOT NULL auto_increment,
res varchar(5) NOT NULL default '',
season tinyint(4) NOT NULL default '0',
x2 tinyint(4) NOT NULL default '0',
PRIMARY KEY (ID),
KEY res (res)
) TYPE=MyISAM; #

Dumping data for table md

INSERT INTO md VALUES ( '1', 'W', '1', '1');

INSERT INTO md VALUES ( '2', 'W', '1', '1');
INSERT INTO md VALUES ( '3', 'D', '1', '1');
INSERT INTO md VALUES ( '4', 'L', '1', '1');
INSERT INTO md VALUES ( '5', 'W', '2', '1');
INSERT INTO md VALUES ( '6', 'D', '2', '1');
INSERT INTO md VALUES ( '7', 'L', '2', '1');
INSERT INTO md VALUES ( '8', 'W', '2', '1');
INSERT INTO md VALUES ( '9', 'L', '2', '1');
INSERT INTO md VALUES ( '10', 'W', '2', '1');

Saludos
Gerardo

[Anon]

Thanks for your (continued) help ...

Tried to understand what you we\'re doing, dont really get it and it doesnt seem to work within my table structure...

Did this

select season_id, (@ws:=\"W\"),(@ds:=\"D\"),(@ls:=\"L\"),
sum(IF(res=@ws,1,res)) as W , sum(IF(res=@ds,1,res)) as D, sum(IF(res=@ls,1,res)) as L
from match_info group by season_id

Only the last three columns return zeros (0)

[tasistro]

NOTE, I am asuming you're using mySQL, check the mySQl online docs for the use of the IF statement and the use of variables in SQLs.

I start with the data structure with contains an ID for the rows, a field with the season (1,2,...etc) and a field
for the res value be it W,D or L

select season, // select season so we can group by it and it will appear on each row
(@ws:="W"), // set variable ws to W for use in IF statement (ws stands for W symbol)
(@ds:="D"),(@ls:="L"), // do the same for variables ds and ls (D and L symbol)
sum(IF(res=@ws,1,res)) as W , // do if statement, if res field equals W symbol return 1 else return res which is
// a non numeric number and appears to be resolving to 0
// if you have problems try
// sum(IF(res=@ws,1,0)), if res=ws return 1 else return 0
// sum basically adds up all the return values for the field res on each row
sum(IF(res=@ds,1,res)) as D, // same as above
sum(IF(res=@ls,1,res)) as L // same as above
from md group by season; // group by season so each returned row has the data of each season

What it does is:

say we have the followin info

ID (row#), season, res
1,1,W
2,1,W
3,1,L
4,1,D
5,2,D
6,2,W

gets row 1, season is 1 so it falls into the 1 group, res is W so
sum(IF(res=@ws,1,res)) as W , returns 1, the first sum field (W)is incremented by 1
sum(IF(res=@ws,1,res)) as D , returns 0, the second sum field (D) is NOT incremente by 1
sum(IF(res=@ls,1,res)) as L , returns 0, the third sum field (L) is NOT incremente by 1

current column values season=1,W=1,D=0,L=0

gets row 2, season is 1 so it falls into the 1 group, res is W so
sum(IF(res=@ws,1,res)) as W , returns 1, the first sum field (W)is incremented by 1
sum(IF(res=@ws,1,res)) as D , returns 0, the second sum field (D) is NOT incremente by 1
sum(IF(res=@ls,1,res)) as L , returns 0, the third sum field (L) is NOT incremente by 1

current column values season=1,W=2,D=0,L=0

gets row 3, season is 1 so it falls into the 1 group, res is L so
sum(IF(res=@ws,1,res)) as W , returns 0, the first sum field (W)is NOT incremented by 1
sum(IF(res=@ws,1,res)) as D , returns 0, the second sum field (D) is NOT incremente by 1
sum(IF(res=@ls,1,res)) as L , returns 1, the third sum field (L) is incremente by 1

current column values season=1,W=2,D=0,L=1

gets row 4, season is 1 so it falls into the 1 group, res is D so
W + 0
D + 1
L + 0

gets row 5, season is 2 so it falls into the 2 group, res is D so
W = 0 // equal sign since we are not incrementing previous values, it is a new group for season=2, so columns are set to 0
D = 1 // first row we find increments D
L = 0

current column values season=1,W=2,D=0,L=1 // we are done with season 1
current column values season=2,W=0,D=0,L=1 // added a second row to sql results with season=2

gets row 6, season is 2 so it falls into the 2 group, res is W so
W + 1
D +0
L+0

current column values season=1,W=2,D=0,L=1
current column values season=2,W=1,D=0,L=1

we are done with all records so the returned table is

season W D L
1 2 0 1
2 1 0 1

Hope it helps

If you keep on having problems send me your data structure to look it up, in the previous sample I posted
the data structure way down

Saludos
Gerardo
[e-mail]