mysqlsqlcountgroupingmedoo

How to count values with different where clauses on the same column?

I hate that I have to ask but I just cant handle it.

I have this table votes:

enter image description here

type=1 is an upvote, type=0 would be a downvote

I want this output:

[
  {'video': 'best-of-mehrwert', 'upvote': 2, 'downvote': 0},
  {...}
]

I am using medoo:

<?php
$votes = $database->query(
  // 'SELECT COUNT(video) as votes, video FROM votes GROUP BY video, type'
  '
    SELECT video,COUNT(*) as counts
    FROM votes
    GROUP BY video,type;

  '
)->fetchAll(PDO::FETCH_ASSOC);

echo json_encode($votes);

which gives me

[{"video":"anlaesse","counts":"1"},{"video":"best-of-mehrwert","counts":"2"}]

How do I "add a column" like "upvotes" i.e. entries where type = 1 and the same with type = 0?

Solution

  • I think it might be easiest to SUM up a 1 for each row where your criteria matches:

    SELECT 
    video, 
    SUM(CASE type WHEN 1 THEN 1 ELSE 0 END) as upvotes,
    SUM(CASE type WHEN 0 THEN 1 ELSE 0 END) as downvotes 
FROM 
    votes
GROUP BY 
    video;

    Note, you should omit type from the GROUP BY in order to get a single row back for each video.