Mysql GROUP Command

I have a query that pulled out the report depends on Audit Date, but I'm very confuse on how the GROUP command is not working on what I'm expecting for the output.

Here is my queries,

SELECT prd.fldemployeeno `EmployeeNo`,
       prd.fldorderid `OrderNo`,
       prd.fldstarttime `TimeProcessed`,
       COUNT(qua.seqid) `ErrorCount`,
       COALESCE(qua.fldstarttime,(SELECT fldstarttime FROM tblproductionitl p
                                   WHERE (p.fldglobalid = prd.fldglobalid)
                                     AND p.fldprojectgroup=prd.fldprojectgroup
                                     AND p.fldstarttime > prd.fldstarttime
                                     AND prd.fldemployeeno != p.fldemployeeno
                                   LIMIT 0,1)) AS `AuditDate`
  FROM tblproductionitl prd
 INNER JOIN tblisauditeditl aud
    ON prd.fldglobalid=aud.fldid
  LEFT JOIN tblqualityaudit qua
    ON prd.fldglobalid=qua.fldid
   AND prd.fldstarttime=qua.fldprodstarttime
 GROUP BY prd.fldemployeeno,prd.fldorderid
 HAVING `AuditDate` BETWEEN '2011-10-04 00:00:00' AND '2011-10-04 23:59:59'
 ORDER BY `AuditDate`

And the output of this is

+-------------+---------------+---------------------+------------+---------------------+
| EmployeeNo  |   OrderNo     | TimeProcessed       | ErrorCount |  AuditDate          |
+-------------+---------------+---------------------+------------+---------------------+
| PSAA50577   | 20110930n01   | 2011-10-04 10:41:23 |   3        | 2011-10-04 10:44:07 |   
| PSAA50576   | 20111003n01   | 2011-10-03 11:39:52 |   1        | 2011-10-04 10:58:48 |
| PSAA50515   | 20110930n01   | 2011-10-04 10:44:07 |   1        | 2011-10-04 11:12:03 |
| PSAA50577   | 20111003n02   | 2011-10-03 12:22:33 |   1        | 2011-10-04 16:47:16 |
| PSAA50577   | 20110930n10   | 2011-10-01 18:27:09 |   1        | 2011-10-04 18:29:29 |
+-------------+---------------+---------------------+------------+---------------------+

And then I have removed the prd.fldorderid in the GROUP command so that the report will be grouped base from EmployeeNo only. But the output returns only 1 row instead of three. Please see below query and output.

SELECT prd.fldemployeeno `EmployeeNo`,
       prd.fldorderid `OrderNo`,
       prd.fldstarttime `TimeProcessed`,
       COUNT(qua.seqid) `ErrorCount`,
       COALESCE(qua.fldstarttime,(SELECT fldstarttime FROM tblproductionitl p
                                   WHERE (p.fldglobalid = prd.fldglobalid)
                                     AND p.fldprojectgroup=prd.fldprojectgroup
                                     AND p.fldstarttime > prd.fldstarttime
                                     AND prd.fldemployeeno != p.fldemployeeno
                                   LIMIT 0,1)) AS `AuditDate`
  FROM tblproductionitl prd
 INNER JOIN tblisauditeditl aud
    ON prd.fldglobalid=aud.fldid
  LEFT JOIN tblqualityaudit qua
    ON prd.fldglobalid=qua.fldid
   AND prd.fldstarttime=qua.fldprodstarttime
 GROUP BY prd.fldemployeeno
 HAVING `AuditDate` BETWEEN '2011-10-04 00:00:00' AND '2011-10-04 23:59:59'
 ORDER BY `AuditDate`

And the output for this query is:

+------------+--------------+---------------------+--------------+---------------------+
| EmployeeNo |  OrderNo     | TimeProcessed       | ErrorCount   |  AuditDate          |
+------------+--------------+---------------------+--------------+---------------------+
| PSAA50576  |  20111003n01 | 2011-10-03 11:39:52 | 1 开发者_Python百科           | 2011-10-04 10:58:48 |
+------------+--------------+---------------------+--------------+---------------------+

Can anyone help me to analyze this on how only 1 row returned in second query and on how could I group the output base from Employee no.

---

You should check the way you are grouping, from MySQL doc:

MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. This means that the preceding query is legal in MySQL. You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate. Furthermore, the selection of values from each group cannot be influenced by adding an ORDER BY clause. Sorting of the result set occurs after values have been chosen, and ORDER BY does not affect which values the server chooses

Edit to explain. Given this example:

  SELECT column1
       , column2
GROUP BY column1

If the table can have different column2 given one value of column1 this is unsafe because anytime you execute the query you could get a different value of column2. In your subquery you are doing this and you should rewrite the query to avoid it.

---

Group by collapses all rows with the same value in the given column.

Note though that ErrorCount is 1 for you single row.
That normally means that there really is only 1 row that meets the criteria of that query.
In other words the output will be the same with and without the group by.

Try changing the definition of ErrorCount to count(*) as ErrorCount.
If errorcount is still 1. Your underlying data has changed so that there's only one row being selected.