473,322 Members | 1,526 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

GROUP BY clause not working

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

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
Jun 2 '06 #1
2 2098
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
Jun 2 '06 #2
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
You have only told it *what* to group together, but it also needs to
know *how* it should do it.
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?


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
Jun 2 '06 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

10
by: Randell D. | last post by:
Folks, I have a SELECT that returns with multiple records - It works when I have a LIMIT clause but when I try to include a GROUP BY clause, the select returns nothing (ie no records, no...
3
by: Robby McGehee | last post by:
I need this to work: SELECT FROM WITH (NOLOCK) where ='a' GROUP BY , HAVING COUNT () > 1 The problem is that I get an error that needs to be in the GROUP BY clause or aggregate function. if...
2
by: Burt | last post by:
MS has been nice enough to add the Cube and Rollup operators so I can have totals with my results. But out of shear meanness they won't let me use them unless I use a Group By clause in my...
4
by: Chad Richardson | last post by:
I've always been mistified why you can't use a column alias in the group by clause (i.e. you have to re-iterate the entire expression in the group by clause after having already done it once in the...
2
by: Paxton | last post by:
Hi, I'm trying to display the total page views per page within a given date range, but the correct SQL is seemingly beyond me. I get the correct result with a straightforward Group By and Count...
2
by: aj70000 | last post by:
This is my query select ano,max(date),a_subject from MY_TAB where table_name='xyz' and ano=877 group by a_subject,ano order by a_subject ANO max(Date) A_Subject 877 2005-01-20...
2
by: JJA | last post by:
Please advise on how to get the GROUP BY coded in an acceptable way: DECLARE @LO INT DECLARE @HI INT DECLARE @StartDate varchar(10) DECLARE @EndDate varchar(10) SELECT @StartDate =...
12
by: Bill Moran | last post by:
Hey all. I've hit an SQL problem that I'm a bit mystified by. I have two different questions regarding this problem: why? and how do I work around it? The following query: SELECT GCP.id,...
6
by: MM | last post by:
Hello users. I have a query that I can run and see the result on the grid on design time, but when i run the program i get the folowing message:ORA-00979: not a GROUP BY expression. When I run the...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.