SQL query involving group by and join

Question

I could not be more specific in terms of the name, but I want to do something a little complicated for me. I thought I did, but it turned out to be buggy.

I have three tables:

Projecttable

• idProject
• title
• idOwner

OfferTable

• idOffer
• idProject
• idAccount

AccountTable

• idAccount
• Username

Now, in one request, I am going to list all projects with the majority of the proposals made, and in the request I also want to receive data, such as the name of the owner, the name of the provider * , etc. Therefore, I do not need to request each project again.

Here is my broken request, this is my first experiment with GROUP BY, and I probably did not quite understand it.

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 

*: I wrote that, without thinking, I was just trying to come up with an example of additional information, which is pointless thanks to the Khosam Ali.

Answer 1

Try this (changed for projects with no suggestions):

 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 

Answer 2

I can change the request a bit:

 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 

This way you get count using projectid and not based on all the other fields you group. I also use LEFT JOIN in case the projectid or another identifier does not exist in other tables, you will return the data anyway.

Answer 3

Your question is a bit vague, but here are a few pointers:

• To list projects “with most proposals made,” ORDER BY offercount .
• You essentially request projects, so you must GROUP BY Project.idProject before the other fields.
• You ask for the number of proposals made for each project, but you ask about the details of the proposal. Actually, it makes no sense (syntactically) to ask for two pieces of information together. If you want to get the total number of sentences repeated in each record of the result, together with information about the sentences, you will have to use an internal query for this.

An internal query can be made either in the FROM , as suggested by other answers, or directly in the SELECT , for example:

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