I must make a query that shows the number of clients per country.
I already have the Country (IdPais, Name) and Client (CustomerID, Name, IdPais) tables, but when I try to do it, I get the error:
"Incorrect syntax near '*'. Expecting HOLDLOCK, or ID".
Here is the code:
Select
Cliente.Nombre, Cliente.IdPais
from
Cliente
left outer join
Pais
count(*) on
Pais.IdPais = Cliente.IdPais
group by
Cliente.idCliente,Cliente.IdPais
Thanks in advance.
Obviously, you can not use the COUNT() function in the middle of a join. In this case, you need it in clause SELECT .
To obtain the number of clients per country, you need to make a join between the 2 tables grouping by country. And, in fact, the ideal is that it is a LEFT JOIN , so that it correctly includes the countries that do not have clients. Although your query does a LEFT JOIN as well, you're doing it from the wrong table. This would be the corrected query:
select p.IdPais, p.Nombre, count(c.IdCliente) as cantidad_clientes
from Pais p
left join Cliente c
on c.IdPais = p.IdPais
group by p.IdPais, p.Nombre
You can not use the count after the from , all the fields that you want to show, must be defined after the statement select
Select p.IdPais,count(idCliente) as total_clientes
from Pais p, Cliente c
where p.IdPais = c.IdPais
group by p.IdPais;
You have to count the number of clients grouping by country:
Select count(c.idCliente) num_clientes, p.idPais
from cliente c, pais p
where c.idPais=p.idPais
group by p.idPais