MYSQL Aggregate function help
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?
Code:
$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)";
Re: MYSQL Aggregate function help
Try something like this instead:
Code:
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
Re: MYSQL Aggregate function help
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
Re: MYSQL Aggregate function help
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.
Code:
$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
Code:
<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.
