[SOLVED] COUNT Help - Fairly Complex, I think :p

[mfallon]

OK, so I have got the following SELECT working with a couple of COUNTs.

 

SELECT users.username, users.firstname, users.lastname, users.status, straights.username, COUNT( straights.username ) AS stotal, MIN( straights.date ) AS sdate, straights.status, COUNT( straights.typeid ) AS stype, straights.typeid
FROM straights
JOIN users ON straights.username = users.username
WHERE straights.status = '1'
AND users.status = '1'
GROUP BY straights.username, straights.typeid
ORDER BY users.lastname, users.firstname

 

It produces the following table;

 

First Name

Surname

No. Straights

First Straight

Type

Total Type

Matt

Liverpool

1

2009-03-01

1

1

Matt

Liverpool

1

2005-03-01

1

2

Bob

Saggett

2

2006-03-01

2

1

 

I can provide the PHP I am using also however I will show you how I want it to display the table.

 

First Name

Surname

No. Straights

First Straight

Type 1 Total

Type 2 Total

Matt

Liverpool

1

2005-03-01

1

1

Bob

Saggett

2

2006-03-01

2

0

 

I tried to do this by adding additional COUNTs which create a total of the each type to display in the row, but couldn't work out how to do it.

 

Any advice on how to get this to work would be appreciated.

 

If I can't get the data on to one row, then I have provided the PHP I am using to display the data on the page below in case anyone can advise how I might be able to amend it to get it to display as I want, however I'm sure I should be able to do it in MySQL.

 

<?   /* Display Straights Table */
function displayStraights(){
   global $database;
   $q = "SELECT users.username,users.firstname,users.lastname,users.status,straights.username,COUNT(straights.username) AS stotal,MIN(straights.date) AS sdate,straights.status,COUNT( straights.typeid ) AS styptot,straights.typeid "
	."FROM straights "
	."JOIN users ON straights.username = users.username "
	."WHERE straights.status = '1' AND users.status = '1' "
	."GROUP BY straights.username, straights.typeid "
	."ORDER BY users.lastname,users.firstname ";
   $result = $database->query($q);
   $num_rows = mysql_numrows($result);
   if(!$result || ($num_rows < 0)){
      echo "Error displaying info";
      return;
   }
   if($num_rows == 0){
      echo "<br>There are no straights currently listed in the Database.";
      return;
   }
   /* Display table contents */
   echo "<p><table width=\"100%\" align=\"center\" border=\"1\" cellspacing=\"0\" cellpadding=\"3\" class=\"sortable\">\n";
   echo "<tr bgcolor=\"#CCCCCC\"><td width=\"70\"><b>Member No</td><td><b>First Name</td><td><b>Surname</td><td width=\"80\"><b>No. Straights</td><td width=\"80\"><b>First Straight</td><td>Type Total</td><td>Type</td></tr>\n";
   for($i=0; $i<$num_rows; $i++){
      $fname  = mysql_result($result,$i,"users.firstname");
      $lname  = mysql_result($result,$i,"users.lastname");
      $stotal  = mysql_result($result,$i,"stotal");
      $sdate  = mysql_result($result,$i,"sdate");
      $styptot  = mysql_result($result,$i,"styptot");
      $stype  = mysql_result($result,$i,"straights.typeid");

   echo "<tr><td> </td><td>$fname</td><td>$lname</td><td>$stotal</td><td>$sdate</td><td>$styptot</td><td>$stype</td></tr>\n";
   }
   echo "</table><br><br></p>\n";
}
?>

 

I know it uses a table, but I'm not clued up enough to use DIVs 

 

Thanks again for any help that anyone can provide.

[aschk]

As a quicker pointer, you appear to want to be grouping by firstname, surname, so I suggest you alter your SQL statement to reflect that...

[kickstart]

Hi

 

Not quite sure what you want to do.

 

I think that you are trying to get a list of users with then a column of a count of each type of record on another table for that user.

 

I do not know a way of doing this in 1 SQL statement if the number of counts is unknown (would love to hear any suggestions).

 

However if you know the number of count columns that you need then something like (pseudo SQL here):-

 

SELECT a.username, (SELECT COUNT(*) FROM straights b WHERE b.username = a.username AND type = 1) AS Type1Count, (SELECT COUNT(*) FROM straights c WHERE c.username = a.username AND type = 2) AS Type2Count FROM users a

 

All the best

 

Keith

[mfallon]

Thanks Keith, worked like a charm.

 

I had tried nested SELECTs but just couldn't seem to get them to work, although I guess that must have been an error in syntax on my part.

 

Below is the code I used in the end minus the other 7 type IDs which I was searching for.

 

Thanks again, Matt

 

SELECT users.username, straights.username, users.firstname, users.lastname, COUNT( straights.username ) AS stotal, MIN( DATE_FORMAT( straights.date, '%d/%m/%Y' ) ) AS sdate, (

SELECT COUNT( straights.typeid ) 
FROM straights
WHERE users.username = straights.username
AND straights.status =1
AND straights.typeid =1
) AS type1tot, (

SELECT COUNT( straights.typeid ) 
FROM straights
WHERE users.username = straights.username
AND straights.status =1
AND straights.typeid =2
) AS type2tot
FROM straights
JOIN users ON straights.username = users.username
WHERE straights.status = '1'
AND users.status = '1'
GROUP BY straights.username
ORDER BY users.lastname, users.firstname