SQL - problema con una consulta sql

Hola a todos tengo un problema con una consulta sql dos tablas detalladas asi:

Tabla hospital.

Cod_hospital │ nombre
01│ san martin
02│ san pedro

Tabla medicos.

Cod_hospital│dni│nombre│especialidad
01│234234│ juan│medico
02│5234234│ martin│cirujano
01│3523525│dario│dentista
02│4234234│ruben│cirujano

Estoy trabjando en una consulta que me ordene el siguiente resultado:

Nombre_hospital│especialidad│numero_especialistas
San martin│medico│1
San martin│dentista│1
San pedro│cirujano│2

Para ello realice el siguiente script y nofunciona probe arreglar de una u otra forma y no se como, gracias por vuestras sugerencias.

No dices cual es el problema ni tampoco que quieres hacer, dices que no funciona, ¿El que no funciona?

Un saludo.

Hola Frex, en realidad no estás tan perdido con lo que estás intentando hacer, sin embargo tienes algunos problemas con tu consulta.

Tal como lo menciona olbeup, debes comenzar por decirnos exactamente cual es el problema con la consulta que intentaste, si tiene error, pues entonces postear el error, si no regresa lo que quieres, entonces decir qué está regresando para saber cómo corregirla.

De entrada y a simple vista veo dos problemas:

1. Las dos tablas tienen un campo llamado NOMBRE, por lo tanto tienes que especificar a qué tabla corresponde el campo, pues de lo contrario te marcará un error de ambiguedad.

2. tienes que hacer un COUNT, pero agrupando por dos campos, es decir, para cada HOSPITAL y cada ESPECIALIDAD te interesa saber CUANTOS DOCTORES hay... tal como lo tienes lo que estás contando seria algo así como CUANTAS ESPECIALIDADES TIENE CADA HOSPITAL.

No olvides mencionar también con qué BD's estás trabajando, ya que hay diferencias importantes entre cada una de ellas.

Saludos
Leo.

Gracias por su respuesta al final ya se soluciono, de nuevo gracias.