CODEWITHSUNDEEP
sqlms-access
Hoser
Hoser

Reputation: 5044

Why doesn't this GROUP BY query work?

I'm querying my Access table with this query:

SELECT (VIN&' '&Make&' '&Model&' '&CarYear&' '&ExColor&' '&InColor&' ')as CarDescript
        FROM TestTable
        WHERE (WorkOrderNumber='$workorder')
        GROUP BY AssignedEmp;

But a similar type of query works just fine in this SQL Fiddle

Even if I replace the long (VIN&' '&....) with VIN it still doesn't work.

EDIT: Schema of the table is

WorkOrderNumber - Priority - JobStage - WorkItem - AssignedEmp - DueDate - VIN - Make - ... - InColor

Upvotes: 1

Views: 993

Answers (1)

Hogan
Hogan

Reputation: 70528

In general use + instead of & for SQL. (Access will allow this however).

In a group by you need to pick which one in the group to use (if you are using mysql like your example it just picks a random one, see this fiddle) so to fix this in the general case for your example:

SELECT (max(VIN) + ' ' + max(Make) + ' ' + max(Model) + ' ' + max(CarYear) + ' ' + max(ExColor) + ' ' + max(InColor) + ' ')
       as CarDescript
FROM TestTable
WHERE WorkOrderNumber='$workorder'
GROUP BY AssignedEmp;

Upvotes: 3

Related Questions