CODEWITHSUNDEEP
mysqlaggregate-functions
Jonnny
Jonnny

Reputation: 5039

Mysql COUNT sub query

I have a fairly complex query for myself across a few tables that I have nearly finished. The final aspect involves just aggregating how many bids an item has received, I am doing this for all items in a particular section though. The query works fine except it only returns 1 row when I try and add in aggregation (/* */) to the query. Reading around it would seem i need a sub query, except I'm not entirely sure how to go about this. This is the query to date:

SELECT s.id as section_id, s.name as section, i.id as item_id, 
i.title as item_title, item.location_id as item_location_id,
i.slug as item_slug, i.description as item_description, 
i.price as item_price, UNIX_TIMESTAMP(i.date_added) as item_date_added, 
c.id, c.city, c.particular_id, p.id, p.particular /*,COUNT(o.i_id) as interest*/

FROM section as s
INNER JOIN item as i
ON s.id = i.section_id
INNER JOIN city as c
ON i.location_id = c.id
INNER JOIN particular as p
ON c.particular_id = p.id

/*LEFT JOIN offer as o
ON o.job_id = i.id*/

WHERE s.id = 2

The code works in that it returns all the rows I expected until the introduction of the /* */ code, where it now only returns 1 row.

any help you could give me would be appreciated

Thanks

Jonny

Upvotes: 0

Views: 1475

Answers (2)

medina
medina

Reputation: 8169

It should solve your problem:

SELECT s.id as section_id, s.name as section, i.id as item_id, 
    i.title as item_title, item.location_id as item_location_id,
    i.slug as item_slug, i.description as item_description, 
    i.price as item_price, UNIX_TIMESTAMP(i.date_added) as item_date_added, 
    c.id, c.city, c.particular_id, p.id, p.particular,
    (SELECT COUNT(o.i_id) FROM offer AS o ON o.job_id = i.id) AS interest
FROM section as s
INNER JOIN item as i
    ON s.id = i.section_id
INNER JOIN city as c
    ON i.location_id = c.id
INNER JOIN particular as p
    ON c.particular_id = p.id
WHERE s.id = 2

Upvotes: 1

Rob
Rob

Reputation: 11733

You have to have group bys if you are doing aggregates, e.g.:

     SELECT s.name, COUNT(i.id)
       FROM section as s
 INNER JOIN item as i
   GROUP BY s.name

would return the section name and the number of items found in it.

Upvotes: 3

Related Questions