CODEWITHSUNDEEP
mysqlsql
tim peterson
tim peterson

Reputation: 24305

JOINing 3 tables, SUM() values from 2 of the tables (MYSQL)

I'm confused on how to get the # of purchases AND the vote count for each item.

Doing two simple JOINs to connect the following 3 tables multiplies the purchase_count by the number of entries in the Votes table: 

SELECT item_id, item_name, sum(backing_status) purchase_count, sum(vote) vote_count 
FROM Items
LEFT JOIN Purchases ON Items.item_id=Purchases.item_id
LEFT JOIN Votes ON Items.item_id=Votes.item_id
where purchase_status='bought' 
group by Items.item_id 

Table Items
item_id item_name
1           item_1
2           item_2
3           item_3

Table Purchases
item_id purchase_status 
1           bought          
2           bought          
1           bought          

Table Votes
item_id    vote 
1           1         
2           1          
3           -1         
1           -1
1           -1

the desired output is: 

item_id item_name  purchase_count vote_count
1           item_1      2          -1 //sum of -2 downvotes + 1 upvote
2           item_2      1           1
3           item_3      0          -1

I probably need to subquery but can't quite figure out the query. Or is there a better way?

Upvotes: 0

Views: 272

Answers (2)

Taryn
Taryn

Reputation: 247670

You should be able to use the following code which uses subqueries with both aggregates to get the result:

select i.item_id,
  i.item_name,
  coalesce(p.TotalBought, 0)TotalBought,
  coalesce(v.vote_count, 0) vote_count
from items i
left join
(
  select item_id, count(*) TotalBought
  from purchases
  where purchase_status = 'bought'
  group by item_id
) p
  on i.item_id = p.item_id
left join
(
  select item_id, sum(vote) vote_count
  from votes
  group by item_id
) v
  on i.item_id = v.item_id;

See SQL Fiddle with Demo.

You could also write this without two subqueries but you will need to place the WHERE filter of purchase_status on the JOIN:

select i.item_id,
  i.item_name,
  count(p.item_id) TotalBought,
  coalesce(v.vote_count, 0) vote_count
from items i
left join purchases p
  on i.item_id = p.item_id
left join
(
  select item_id, sum(vote) vote_count
  from votes
  group by item_id
) v
  on i.item_id = v.item_id
group by i.item_id, i.item_name;

See SQL Fiddle with Demo

Upvotes: 1

Lucia Pasarin
Lucia Pasarin

Reputation: 2308

SELECT item_id, item_name, sum(backing_status) AS purchase_count, SUM(vote) 
    AS vote_count
FROM Items i
LEFT JOIN Purchases p on i.item_id = p.item_id
LEFT JOIN (SELECT SUM(vote), item_id FROM votes GROUP BY item_id) AS grv 
    on grv.item_id = i.item_id
WHERE purchase_status='bought';

Upvotes: 0

Related Questions