MYSQL Aggregate function help

[seanwpb]

i have the following and the COUNT(recnum) AS TotalCount counts all the records with a certain siteid and it works fine but i now need to know out of that total how many are completed so i tried COUNT(completeddate != '0000-00-00') AS TotalCompleted and thats not working. it just gives me the same as the first aggregate function. i tried to include some if statements but that didnt work either. any ideas?

$query="SELECT siteid, completeddate, recnum, COUNT(recnum) AS TotalCount, COUNT(completeddate != '0000-00-00') AS TotalCompleted From WorkOrders GROUP BY siteid ORDER BY CAST(siteid AS UNSIGNED)";

[RhinoBull]

Try something like this instead:

SELECT
    siteid,
    completeddate,
    recnum,
    (select COUNT(recnum) from WorkOrders GROUP BY siteid) AS TotalCount,
    (select COUNT(completeddate) from WorkOrders whhere completeddate != '0000-00-00') AS TotalCompleted
From
    WorkOrders
ORDER BY siteid

[seanwpb]

thanks for the response. i get the error

Warning: mysql_query() [function.mysql-query]: Unable to save result set in /data/24/2/123/150/2286802/user/2512997/htdocs/ReportAdminAllGroupedTickets.php on line 218

Warning: mysql_numrows(): supplied argument is not a valid MySQL result resource in /data/24/2/123/150/2286802/user/2512997/htdocs/ReportAdminAllGroupedTickets.php on line 220

line 218 and 220 are

$result=mysql_query($query);

$num=mysql_numrows($result);

i tried a subquery already but different from your with no success

[seanwpb]

here is what i did i dont get an error but if dont get the right results and i think its a grouping issue. so basically my TotalCompleted and TotalNotCompleted is all of them for all the siteid's combined.

$query="SELECT siteid, completeddate, recnum, 
COUNT(recnum) AS TotalCount, (select COUNT(completeddate) from WorkOrders where completeddate != '0000-00-00') AS TotalCompleted, (select COUNT(completeddate) from WorkOrders where completeddate = '0000-00-00') AS TotalNOTCompleted 
From WorkOrders 
GROUP BY TotalCompleted, TotalNOTCompleted, siteid 
ORDER BY CAST(siteid AS UNSIGNED)";

so my report which is

<td><font size="1" face="Arial, Helvetica, sans-serif"><center><? echo $siteid; ?></center></font></td>
<td><font size="1" face="Arial, Helvetica, sans-serif"><center><? echo $TotalCount; ?></center></font></td>
<td><font size="1" face="Arial, Helvetica, sans-serif"><center><? echo $TotalNOTCompleted; ?></center></font></td>
<td><font size="1" face="Arial, Helvetica, sans-serif"><center><? echo $TotalCompleted; ?></center></font></td>

it groups siteid fine but in every cell in the report for TotalNOTCompleted is the total not completed for the entire database and same for totalcompleted. so i need help with my grouping.