Posted by Janwillem Borleffs on 06/02/06 21:50

Lorenzo Thurman wrote:
> I'm using PHP 5 to make an ODBC connection to an MS Access database
> using PEAR and I'm getting DB Error: Syntax Error when using the GROUP
> BY clause. Here's my query:
> SELECT * FROM HardwareInstallationsSummary WHERE LocationID = $loc_ID
> GROUP BY EquipmentTypeID
>

In standard SQL, you cannot use columns in a query containing a GROUP BY
clause, unless they are part of the clause or an aggregate function.

Examples:

SELECT LocationID FROM HardwareInstallationsSummary WHERE LocationID =
$loc_ID
GROUP BY EquipmentTypeID,LocationID;

SELECT count(*) FROM HardwareInstallationsSummary WHERE LocationID = $loc_ID
GROUP BY EquipmentTypeID;

In the last example, the wildcard match is accepted, because it's used in an
aggregate function (count).

Note that there are some DBMS's which allow you to break this rule, like
MySQL.


JW

[Back to original message]