'MYSQL GROUP BY and SUM with clause
Hello there are two tables
Interval
| id | is_full |
|---|---|
| 1 | 1 |
| 2 | 0 |
| 3 | 0 |
entry_penalty
| interval_id | entry_id | amount |
|---|---|---|
| 2 | 14 | 55 |
| 3 | 14 | 7 |
| 3 | 14 | 1 |
| 1 | 15 | 4 |
| 1 | 15 | 8 |
| 2 | 15 | 11 |
So i am trying to display Sum of all entry_penalties per interval, twist is even if there is no relation between entry_penalty and interval table i should display full course interval sum per entry_id (related to is_full field).
For example total results should be in this case
| interval_id | entry_id | amount |
|---|---|---|
| 1 | 14 | 63 |
| 2 | 14 | 55 |
| 3 | 14 | 8 |
| 1 | 15 | 23 |
| 2 | 15 | 11 |
I have tried with sub query but it ignores to do calculation when there is no relation between entry_penalties and interval tables regarding is_full column. My code so far.
SELECT
ep.interval_id,
IF (
i.is_full,
(
SELECT SUM(ep2.amount) * 1000 FROM entry_penalty as ep2
WHERE ep2.entry_id = ep.entry_id
),
SUM(ep.amount) * 1000
) as penalty_time,
ep.entry_id
FROM entry_penalty ep
INNER JOIN \`interval\` i ON i.id = ep.interval_id
WHERE ep.entry_id IN (:entryIds)
GROUP BY interval_id, entry_id`
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
