Page 1 of 1

GROUP BY, DISTINCT, COUNT..... WHICH IS IT???

Posted: Mon Dec 15, 2008 8:43 am
by simonmlewis
Hi all

I need a genius out there somewhere to find me the solution for something that sounds so very easy.

I have a log of searched entries as an SQL database table. It has just two fields: id, department.

I was to produce a page that shows each individual department searched on (not duplicated - ie. GROUP BY and DISTINCT), with the number next to it showing how many entries of it there are.

ie. there are 25 entries of "commercial", so the result would render as:

Department hits
commercial 25

I have tried the following:

Code: Select all

$query = mysql_query ("SELECT DISTINCT department, COUNT(DISTINCT department) as num_off FROM departmentlog GROUP BY department") or die (mysql_error());
 
while($row = mysql_fetch_array($result)){
echo "$row->department There are " .$row['num_of'] ."finished.";
}
But I get this as a result:
Warning: mysql_fetch_array(): 3 is not a valid MySQL result resource in other/deptlog.php on line 33
Can anyone please help.
Thank you.
Simon

Re: GROUP BY, DISTINCT, COUNT..... WHICH IS IT???

Posted: Mon Dec 15, 2008 8:47 am
by Mark Baker
Drop the distinct, it's meaningless in this context because you're doing a group by on department

Code: Select all

 
$query = mysql_query ("SELECT department, COUNT(*) as num_off FROM departmentlog GROUP BY department")
   or die (mysql_error());
 
while($row = mysql_fetch_array($result)){
   echo $row['department'] . " There are " . $row['num_of'] . " finished.<br />";
}
 

Re: GROUP BY, DISTINCT, COUNT..... WHICH IS IT???

Posted: Mon Dec 15, 2008 12:05 pm
by simonmlewis
Same error:
Warning: mysql_fetch_array(): 3 is not a valid MySQL result resource in /other/deptlog.php on line 33
Line 33 is this:

Code: Select all

while($row = mysql_fetch_array($result)){
Total code now shows:

Code: Select all

<?php 
$sqlconn=mysql_connect("localhost","U","P");
$rs=mysql_select_db("DB",$sqlconn);
$query = mysql_query ("SELECT department, COUNT(*) as num_off FROM departmentlog GROUP BY department") or die (mysql_error());
 
while($row = mysql_fetch_array($result)){
echo "$row->department There are " .$row['num_of'] ."finished.<br/>";
}
 
    mysql_close($sqlconn);
?>

Re: GROUP BY, DISTINCT, COUNT..... WHICH IS IT???

Posted: Mon Dec 15, 2008 12:12 pm
by Mark Baker

Code: Select all

 
$result = mysql_query ("SELECT department, COUNT(*) as num_off FROM departmentlog GROUP BY department")
   or die (mysql_error());
 
while($row = mysql_fetch_array($result)){
   echo $row['department'] . " There are " . $row['num_of'] . " finished.<br />";
}
 

Re: GROUP BY, DISTINCT, COUNT..... WHICH IS IT???

Posted: Mon Dec 15, 2008 12:22 pm
by simonmlewis
That's excellent, thank you very much.

Result was this:
Agricultural Property There are 16 finished.
Associates There are 30 finished.
Business Rescue & Insolvency There are 8 finished.
Commercial Property There are 19 finished.
Company and Commercial There are 13 finished.
Consultants There are 28 finished.
Debt Recovery There are 8 finished.
Well, part of it. I can play around the with text and layout now.

Brilliant!!