GROUP BY clause not working

[Lorenzo Thurman]

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 HardwareInstall ationsSummary WHERE LocationID = $loc_ID
GROUP BY EquipmentTypeID

Just for kicks, I changed the GROUP BY to ORDER BY. The query works, and
the items are sorted as expected, but this is not what I want. Is there
a bug in here somewhere?
TIA

[Janwillem Borleffs]

Re: GROUP BY clause not working

Lorenzo Thurman wrote:[color=blue]
> 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 HardwareInstall ationsSummary WHERE LocationID = $loc_ID
> GROUP BY EquipmentTypeID
>[/color]

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 HardwareInstall ationsSummary WHERE LocationID =
$loc_ID
GROUP BY EquipmentTypeID ,LocationID;

SELECT count(*) FROM HardwareInstall ationsSummary 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

[Bent Stigsen]

Re: GROUP BY clause not working

Lorenzo Thurman wrote:[color=blue]
> 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 HardwareInstall ationsSummary WHERE LocationID = $loc_ID
> GROUP BY EquipmentTypeID[/color]

You have only told it *what* to group together, but it also needs to
know *how* it should do it.
[color=blue]
> Just for kicks, I changed the GROUP BY to ORDER BY. The query works, and
> the items are sorted as expected, but this is not what I want. Is there
> a bug in here somewhere?[/color]

You need to spell it out for the ms-access thingy. As Janwillem
mentions, you use aggregate functions for that, and there might be
differences between different systems. So try at
"comp.databases .ms-access", and give them a bit more information about
the columns of the table you select from, and what you expect to happen.


/Bent