SQL query involving group by and joins

Question

I couldn't be more specific in the title part but I want to do something a little bit complex for me. I thought I did it but it turned out that it is buggy.

I have three tables as following:

ProjectTable

• idProject
• title
• idOwner

OfferTable

• idOffer
• idProject
• idAccount

AccountTable

• idAccount
• Username

Now in one query I aim to list all the projects with most offers made, and in the query I also want to get details like the username of the owner, username of the offerer* etc. So I don't have to query again for each project.

Here is my broken query, it's my first experiment with GROUP BY and I probably didn't quite get 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 example extra information, that is meaningless thanks to Hosam Aly.

Solution 1

Try this (modified for projects with no offers):

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

Solution 2

I might switch the query slightly to this:

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 are getting the count by the projectid and not based on all of the other fields you are grouping by. I am also using a LEFT JOIN in the event the projectid or other id doesn't exist in the other tables, you will still return data.

Solution 3

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

• To list the projects "with most offers made", ORDER BY offercount.
• You're essentially querying for projects, so you should GROUP BY Project.idProject first before the other fields.
• You're querying for the number of offers made on each project, yet you ask about offer details. It doesn't really make sense (syntax-wise) to ask for the two pieces of information together. If you want to get the total number of offers, repeated in every record of the result, along with offer information, you'll have to use an inner query for that.

An inner query can be made either in the FROM clause, as suggested by other answers, or directly in the SELECT clause, like so:

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

Comments

[Hosam Aly]

For clarity, I'd prefer to COUNT(o.idOffer), since we're actually counting offers, not projects.

[EralpB]

Yes, you are right about the 1st point, I have 3 of these queries for different kind of ordering and I copied the wrong one. And I corrected the 3rd point.

[EralpB]

Does this query return projects with 0 offers? I changed this to fit my actual table names but I am not sure if I made a mistake or this query doesn't show them. I changed last two join types to FULL and LEFT, now I get NULL when it is zero. It is not a problem and easy to fix with code but I'd like to learn what would you change to make those NULLs zeroes.