CODEWITHSUNDEEP
mysqlsql
popop
popop

Reputation: 177

COUNT with case statement not working properly

Suppose I have the following ouput from my query:

{
    "player_first_name": "Albano",
    "player_last_name": "Aleksi",
    "yellow_cards": "14",
    "orange_cards": "0",
    "red_cards": "1",
    "points": "15",
    "player_id": "286635"
}

as you can see the player have 14 yellow cards, 1 red cards and 0 orange cards.

I want calculate the "point" earned by this playern counting each card in the following way:

so the final result should be: 17

I tried to count the total of the points in the following way:

$sql = $this->db->prepare("SELECT
    p.first_name AS player_first_name,
    p.last_name AS player_last_name,
    COUNT(CASE
      WHEN c.card_id = 1 THEN 1
      END) AS yellow_cards,
    COUNT(CASE WHEN c.card_id = 2 THEN 1 END) AS orange_cards,
    COUNT(CASE
      WHEN c.card_id = 3 THEN 1
      END) AS red_cards,
    COUNT(CASE
      WHEN c.card_id = 1 THEN 1
      WHEN c.card_id = 2 THEN 2
      WHEN c.card_id = 3 THEN 3
      END) AS points,
    p.id AS player_id
    FROM `match` m
    INNER JOIN player_cards c ON c.match_id = m.id
    INNER JOIN player p ON c.player_id = p.id
    WHERE m.round_id = :round_id
    GROUP BY p.id
    ORDER BY points DESC, player_last_name ASC");

as you can see I have the following statement:

 COUNT(CASE
      WHEN c.card_id = 1 THEN 1
      WHEN c.card_id = 2 THEN 2
      WHEN c.card_id = 3 THEN 3
      END) AS points,

the card id correspond the id of the color, so:

why the toal is incorrect?

UPDATE

Expected output:

{
    "player_first_name": "Albano",
    "player_last_name": "Aleksi",
    "yellow_cards": "14",
    "orange_cards": "0",
    "red_cards": "1",
    "points": "17",
    "player_id": "286635"
}

as you can see points is 17 because we have 14 yellow cards (each yellow card is worth 1 point), and we have 1 red card, each red card is worth 3 point, so: 14 + 3 = 17. But I get 15

Upvotes: 0

Views: 2493

Answers (6)

Bohemian
Bohemian

Reputation: 424993

The simplest code is:

SUM(c.card_id) AS points,

If you don't want to depend on card_id having certian values, then this is the next simplest:

SUM((c.card_id = 1) + 2 * (c.card_id = 2) + 3 * (c.card_id = 3))

This works because in MySQL true is 1 and false is 0.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269603

As mentioned in other answers, count() counts the number of non-NULL results. Hence, 0 is as non-NULL as any other value.

But, MySQL also offers a convenient shortcut that doesn't use CASE. Boolean expressions are treated as numbers in a numeric context, with "1" for true and "0" for false. So:

SELECT p.first_name AS player_first_name, p.last_name AS player_last_name,
       SUM( c.card_id = 1 ) AS yellow_cards,
       SUM( c.card_id = 2 ) AS orange_cards,
       SUM( c.card_id = 3 ) AS red_cards,
       SUM( CASE WHEN c.card_id IN (1, 2, 3) THEN c.card_id ELSE 0 END) AS points,
       p.id AS player_id
FROM `match` m INNER JOIN
     player_cards c
     ON c.match_id = m.id INNER JOIN
     player p ON c.player_id = p.id
WHERE m.round_id = :round_id
GROUP BY p.id
ORDER BY points DESC, player_last_name ASC;

If the only allowed card_ids are 0, 1, 2, and 3, then the points calculation can be simplified to:

       SUM( c.card_id ) AS points,

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 94884

Replace 

COUNT(CASE
      WHEN c.card_id = 1 THEN 1
      WHEN c.card_id = 2 THEN 2
      WHEN c.card_id = 3 THEN 3
      END) AS points,

with 

SUM(CASE
    WHEN c.card_id = 1 THEN 1
    WHEN c.card_id = 2 THEN 2
    WHEN c.card_id = 3 THEN 3
    END) AS points,

because with count you just count while you want the sum really. If you count the numbers 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 3 you get fifteen, if you add them up you get seventeen.

However, there is obviously a card table the card_id refers to. This card table should naturally contain the value of the card. So join with the cards table and use

SUM(card.value)

instead of the above.

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520998

Since you just want to sum the numerical value of the card to get the total, then just use SUM(card_id) instead of COUNT:

SELECT
    p.first_name AS player_first_name,
    p.last_name AS player_last_name,
    COUNT(CASE WHEN c.card_id = 1 THEN 1 END) AS yellow_cards,
    COUNT(CASE WHEN c.card_id = 2 THEN 1 END) AS orange_cards,
    COUNT(CASE WHEN c.card_id = 3 THEN 1 END) AS red_cards,
    SUM(c.card_id) AS points,
    p.id AS player_id
FROM match m
INNER JOIN player_cards c
    ON c.match_id = m.id
INNER JOIN player p
    ON c.player_id = p.id
WHERE
    m.round_id = :round_id
GROUP BY
    p.id
ORDER BY
    points DESC, player_last_name;

Your PHP code should then look like this:

$stmt = $connection->prepare();
$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
    $red = $row["red_cards"];
    $yel = $row["yellow_cards"];
    $orange = $row["orange_cards"];
    $points = $row["points"];
}

Upvotes: 3

Tim Huang
Tim Huang

Reputation: 41

Just replace all your count with sum and retry.

Upvotes: 0

squancy
squancy

Reputation: 565

What if you try to count the points by query the cards from the database, save it in variables and then count them all without any inline CASE statements in SQL. Make a simple query where you get each player's red, orange and yellow cards and then count them as in the example.

// The query to get the cards, name etc.
$sql = "SELECT
p.first_name AS player_first_name,
p.last_name AS player_last_name,
c.card_id = 1 THEN 1 AS yellow_cards
c.card_id = 2 THEN 2 AS orange_cards
c.card_id = 3 THEN 3 AS red_cards
p.id AS player_id
FROM `match` m
INNER JOIN player_cards c ON c.match_id = m.id
INNER JOIN player p ON c.player_id = p.id
WHERE m.round_id = round_id
GROUP BY p.id
ORDER BY points DESC, player_last_name ASC";

// Prepared statement to get the cards as variables and then count the points
$stmt = $connection->prepare();
$stmt->execute();
$result = $stmt->get_result();
while($row = $result->fetch_assoc()){
    $red = $row["red_cards"];
    $yel = $row["yellow_cards"];
    $orange = $row["orange_cards"];
    $points = $red * 3 + $orange * 2 + $yel;
}

Please note that I used prepared statements in the example and the $connection variable covers the mySQL connection to the server.

Upvotes: 1

Related Questions