SQL Question - Counting Rows

[tony_l]

I am creating an administration backend to a proposal submission system.

Proposals have several columns in the table. One of these columns is called status - in which there are 10 different statuses.

I am trying to build an overview page that details how many proposals are pending, rejected, etc.

The status is an integer (which is the key to another table that has an id and a status name). Is there a way I can count all of the rows in the proposals table that has a status of 1 (pending) and all the other statuses in the same query? I know I can do something like -

SELECT
COUNT(*)
FROM
proposals
WHERE
status = 1;

But then I would have to do that 9 other times - taking up space and time. I am hoping that there is a way of doing this in one query, instead of 10.

Any help would be appreciated.

[NogDog]

SELECT status, COUNT(*) AS status_count FROM proposals GROUP BY status

[tony_l]

and then how would you retrieve it?

I am not familiar with the GROUP BY method.

I know what it does by looking at it - it would group the results, but when I get the results into an array or object, what happens?

$results = mysql_query($sql);

$obj = mysql_fetch_object($results);

??

[Roger_Ramjet]

The GROUP BY query will return a multi-row result set that you iterate through in the normal way. Depending on your requirements you are probably best to join to the status table to get a full output that lists the text as well as the status id.

$sql = "SELECT status_name, COUNT(status) as count FROM status_names s LEFT JOIN proposals p ON(s.id=p.status) GROUP BY status_name, status";
$result = mysql_query($sql) or die(mysql_error);
while ($obj=mysql_fetch_object($result)) {
   echo $obj->status;
   echo $obj->count;
}