-2
select title, Forename AS "Firstname", Surname AS "Lastname", COUNT 
(VISIT.Vet_Id) AS "Visits"
from VISIT, VET
where VISIT.Vet_Id = VET.Vet_Id
group by title, Surname,VISIT.VET_Id
order by title, Surname, VISIT.VET_Id;

When executing the code above, i receive an error message: Not a GROUP BY expression though it works when i remove Forename AS "Firstname"

Why is this happening?

Lightning pog
  • 1
  • 1
  • 1
    Have you tried searching? There are many questions and answers on this topic – Aleksej Jun 20 '17 at 10:43
  • You have to add `Forename` to the `group by`. If a `group by` is specified, all selected attributes have to be either in the `group by` or in an aggregate. – HoneyBadger Jun 20 '17 at 10:44

2 Answers2

0

Just include the non-aggregated columns in the SELECT in the GROUP BY:

select title, Forename AS "Firstname", Surname AS "Lastname",
       COUNT(vi.Vet_Id) AS "Visits"
from VISIT vi join
     VET v
     on vi.Vet_Id = v.Vet_Id
group by title, Forename, Surname
order by title, Forename, Surname;

And, never use commas in the FROM clause. Always use proper, explicit JOIN syntax. You should also qualify all your column references.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

In case of Oracle database we have to add all non aggregated field in Group by clause which we have taken in Select clause otherwise it gives error. In case of mysql there is no such restrictions for group by clause.

Shrashti
  • 140
  • 5
  • 13
  • 1
    That's not a "restriction". MySQL will simply return random results (and newer versions will also righfully reject such a query) –  Jun 20 '17 at 12:22