Consulta SQL que involucra grupo por y se une

No podría ser más específico en la parte del título, pero quiero hacer algo un poco complejo para mí. Pensé que lo hice, pero resultó que tiene errores.

Tengo tres tablas de la siguiente manera:

ProjectTable

  • idProject
  • título
  • idOwner

OfferTable

  • idOffer
  • idProject
  • idAccount

AccountTable

  • idAccount
  • Nombre de usuario

Ahora, en una consulta, bash enumerar todos los proyectos con la mayoría de las ofertas realizadas, y en la consulta también deseo get detalles como el nombre de usuario del propietario, el nombre de usuario del ofertante *, etc., así que no tengo que volver a consultar cada proyecto

Aquí está mi consulta interrumpida, es mi primer experimento con GROUP BY y probablemente no lo entendí del todo.

SELECT Project.addDate,Project.idOwner ,Account.Username,Project.idProject, Project.Price,COUNT(Project.idProject) as offercount FROM Project INNER JOIN Offer ON Project.idProject= Offer.idProject INNER JOIN Account ON Account.idAccount = Project.idOwner GROUP BY Project.addDate,Project.idOwner, Account.Username,Project.idProject,Project.Price ORDER BY addDate DESC 

*: Escribí que sin pensar solo intentaba aportar información adicional de ejemplo, eso no tiene sentido gracias a Hosam Aly.

Pruebe esto (modificado para proyectos sin ofertas):

 SELECT Project.addDate, Project.idOwner, Account.Username, Project.idProject, Project.Price, ISNULL(q.offercount, 0) AS offercount FROM ( SELECT o.idProject, COUNT(o.idProject) as offercount FROM Offer o GROUP BY o.idProject ) AS q RIGHT JOIN Project ON Project.idProject = q.idProject INNER JOIN Account ON Account.idAccount = Project.idOwner ORDER BY addDate DESC 

Podría cambiar la consulta ligeramente a esto:

 select p.addDate, p.idOwner, a.Username, p.idProject, p.price, o.OfferCount from project p left join ( select count(*) OfferCount, idproject from offer group by idproject ) o on p.idproject = o.idproject left join account a on p.idowner = a.idaccount 

De esta forma, obtiene el count por parte del projectid y no está basado en todos los otros campos por los que está agrupando. También estoy usando un LEFT JOIN en caso de que projectid u otro id no exista en las otras tablas, aún así devolverá los datos.

Su pregunta es un poco vaga, pero aquí hay algunos consejos:

  • Para enumerar los proyectos "con la mayoría de las ofertas realizadas", ORDER BY offercount .
  • Básicamente está buscando proyectos, por lo que debe GROUP BY Project.idProject antes de GROUP BY Project.idProject antes que los otros campos.
  • Está preguntando por la cantidad de ofertas realizadas en cada proyecto, sin embargo, pregunta sobre los detalles de la oferta. Realmente no tiene sentido (syntax) pedir las dos piezas de información juntas. Si desea get el número total de ofertas, repetidas en cada logging del resultado, junto con la información de la oferta, tendrá que usar una consulta interna para eso.

Se puede realizar una consulta interna en la cláusula FROM , tal como lo sugieren otras respuestas, o directamente en la cláusula SELECT , de la siguiente manera:

 SELECT Project.idProject, (SELECT COUNT(Offer.idOffer) FROM Offer WHERE Offer.idProject = Project.idProject ) AS OfferCount FROM Project