complex queries - grouping syntax error

Question

complex queries - grouping syntax error

Navigation

#1 by (1 votes)

-1

I am asked for a consultation in which I must show the doctors assigned to an area and in the second parameter that is in another table how many doctors there are in that area, the truth is that I have a cacau of orders I generated this select

SELECT numArea, count(*)AS Quantitat metges 
FROM hospital.metge 
GROUP BY numArea 
HAVING count(*)>1
WHERE numArea IS NOT NULL;

Of course it does not work, but at first sight there is some syntax error? I mean between FROM GROUP BY HAVING AND WHERE must there be a comma?

mysql-workbench

asked by Carlos 02.11.2017 в 10:49

source

1 answer

Consultation with 5 union Problem loading an image with codeigniter

score 1 · Accepted Answer

I invite you to refresh your knowledge about the SELECT syntax in MySQL

As you can see at first glance, what you have wrong is the clause WHERE , which should be before GROUP BY .

SELECT numArea, count(*)AS Quantitat metges 
FROM hospital.metge 
WHERE numArea IS NOT NULL
GROUP BY numArea 
HAVING count(*)>1;

The second syntax error you might have is in the name assigned to the column that makes the count that you have called Quantitat metges .

You have 2 options:

Include a low script to Quantitat metges , leaving Quantitat_metges .

Include quotes at the beginning and end of the name "Quantitat metges"

SELECT numArea, count(*)AS "Quantitat metges" 
FROM hospital.metge 
WHERE numArea IS NOT NULL
GROUP BY numArea 
HAVING count(*)>1;