CODEWITHSUNDEEP
mysqlsql
naveed
naveed

Reputation: 1465

SQL: Join and Count based on condition

I have 2 tables:

CREATE TABLE Items (id INT, name VARCHAR(8));
CREATE TABLE Likes (item_id INT, usr VARCHAR(8), doeslike BOOLEAN);

INSERT INTO Items VALUES 
    (1, 'Pen'),
    (2, 'Pencil'),
    (3, 'Ruler');

INSERT INTO Likes VALUES
    (1, 'joe', TRUE ),
    (1, 'jill', TRUE ),
    (1, 'jack', FALSE),
    (2, 'jae', TRUE ),
    (2, 'jill', FALSE),
    (2, 'john', FALSE),
    (2, 'jer', TRUE ),
    (3, 'mill', TRUE ),
    (3, 'mack', FALSE),
    (3, 'moe', TRUE ),
    (3, 'gill', TRUE ),
    (3, 'zack', FALSE);

Fiddle here: http://sqlfiddle.com/#!9/d1e0f

I need a query that lists the items and number of people who like and dislike each item, like so:

ITEM    LIKES   DISLIKES
Pen     2       1
Pencil  2       2
Ruler   3       3

Upvotes: 1

Views: 1012

Answers (5)

IHTS
IHTS

Reputation: 31

SELECT 
I.name AS 'ITEM'
,SUM(CASE WHEN L.doeslike = 1 THEN 1 ELSE 0 END) AS 'LIKES'
,SUM(CASE WHEN L.doeslike = 0 THEN 1 ELSE 0 END) AS 'DISLIKES'
FROM ITEMS I
INNER JOIN LIKES L
ON I.ID = L.ITEM_ID
GROUP BY I.id

Upvotes: 2

A_Sk
A_Sk

Reputation: 4630

Use LEFTJOIN to Join the tables and then count the Boolean field to get the Likes and dislikes

select a.name as ITEM,SUM(CASE WHEN b.doesLike THEN 1 ELSE 0 END) as LIKES ,
SUM(CASE WHEN b.doesLike THEN 0 ELSE 1 END) as DISLIKES from Item a left join Likes  b  a.id = b.item_id
GROUP BY a.id

Upvotes: 0

AdamMc331
AdamMc331

Reputation: 16690

You can use conditional aggregation for this. If you use SUM(condition) inside of a select statement, you'll get the number of times that condition is true. Use it like this for your table:

SELECT i.name, SUM(l.doeslike = true) AS likes, SUM(l.doeslike = false) AS dislikes
FROM items i
JOIN likes l ON l.item_id = i.id
GROUP BY i.id;

Here is a Fiddle example. I would also recommend using a LEFT JOIN if there is a chance an item exists without having any likes/dislikes.

Upvotes: 4

WongFaiHung
WongFaiHung

Reputation: 76

something like this might serve your needs:

SELECT count('Likes.usr') as Likes, Items.name AS Product FROM Likes LEFT OUTER JOIN Items ON Items.id = Likes.item_id GROUP BY Product

best regards.

Upvotes: 0

Alex
Alex

Reputation: 17289

http://sqlfiddle.com/#!9/d1e0f/2

SELECT Items.* , 
SUM(CASE WHEN Likes.doesLike THEN 1 ELSE 0 END) as likes,
SUM(CASE WHEN Likes.doesLike THEN 0 ELSE 1 END) as dislikes

FROM Items
LEFT JOIN Likes
ON
 Items.id = Likes.item_id
GROUP BY Items.id

Upvotes: 4

Related Questions