CODEWITHSUNDEEP
sqlpostgresqljoincountaggregate
Shadowman
Shadowman

Reputation: 12049

Query help needed -- aggregates and multiple joins

I have several tables that are used in my application. One maintains a list of products, another maintains comments on those items, another contains star ratings for those items, and the last has the purchases of those items. My tables look something like this:

tbl_item:
---------
id      INT (primary key)
name    VARCHAR (product name)

tbl_comment:
------------
id          INT (primary key)
item_id     INT (foregin key -> tbl_item.id)
commenttext VARCHAR

tbl_rating:
-----------
id          INT (primary key)
item_id     INT (foreign key -> tbl_item.id)
rating      DOUBLE

tbl_purchases:
--------------
id          INT (primary key)
item_id     INT (foreign key -> tbl_item.id)

I would like to execute a query that returns the following:

* The design ID
* The average rating
* The number of comments
* The number of purchases

I had something similar to this, but it returns the incorrect data:

SELECT  d.id ,
        COUNT(tbl_purchases.id) AS purchase_count, 
        COUNT(tbl_comment.id) AS comment_count,
        AVG(tbl_rating.rating) AS item_rating,
    FROM tbl_item d
    LEFT JOIN tbl_purchases ON tbl_purchases.item_id = d.id
    LEFT JOIN tbl_comment ON tbl_comment.item_id = d.id
    LEFT JOIN tbl_rating ON tbl_rating.id = d.id
    GROUP BY d.id;

What I've found is that my COUNT() columns return the same value for both columns, which is definitely not correct. Clearly I'm doing something wrong in my joins or my GROUP BY, but I'm not entirely sure what. I'm a Java guy, not a SQL guy, so I'm not sure what's going wrong in this SELECT statement.

Can anyone give me a hand in constructing this query? Is there a way to perform this aggregate query across several different tables this way? Thanks!!

Upvotes: 2

Views: 242

Answers (3)

Seth Robertson
Seth Robertson

Reputation: 31461

Using count(distinct(tbl_purchases.id)) should resolve your problem without the more complex queries (but also correct) queries others have offered.

Upvotes: 1

frogstarr78
frogstarr78

Reputation: 868

It'll depend somewhat on what db you're using, but this outta work in PostgreSQL:

    SELECT d.id , p.count, c.count, AVG(I.rating)
      FROM tbl_item d
      JOIN ( SELECT count(id), item_id as id from tbl_purchases ) as P
     USING (id)
      JOIN ( SELECT count(id), item_id as id from tbl_comment ) as C
     USING (id)
 LEFT JOIN tbl_rating as I
        ON tbl_rating.id = d.id
  GROUP BY d.id
;

Upvotes: 0

Gareth
Gareth

Reputation: 936

Try this:

SELECT  d.id ,
        COALESCE(t.purchase_count,0) as purchase_count, 
        COALESCE(c.comment_count,0) as comment_count,
        r.item_rating,
    FROM tbl_item d
    LEFT JOIN (SELECT item_id, COUNT(1) as purchase_count from tbl_purchases group by item_id) as t on t.item_id = d.id
    LEFT JOIN (SELECT item_id, COUNT(1) as comment_count from tbl_comment group by item_id) as c ON c.item_id = d.id
    LEFT JOIN (SELECT item_id, AVG(rating) as item_rating from tbl_rating group by item_id) as r ON r.item_id = d.id;

Upvotes: 5

Related Questions