MYSQL GROUP BY and SUM with clause-CodePudding

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`

CodePudding user response：

I would propose to deal with the two cases (full, not full) separately, and then use union all to combine the two results:

SELECT     i.id, ep.entry_id, SUM(ep.amount)
FROM       `interval` i, 
           entry_penalty ep
WHERE      i.is_full
GROUP BY   i.id, ep.entry_id
UNION ALL
SELECT     i.id, ep.entry_id, SUM(ep.amount)
FROM       entry_penalty ep
INNER JOIN `interval` i 
        ON ep.interval_id = i.id 
       AND NOT i.is_full
GROUP BY   i.id, ep.entry_id
ORDER BY   2, 1

See it run on dbfiddle.uk, where it outputs:

id	entry_id	SUM(ep.amount)
1	14	63
2	14	55
3	14	8
1	15	23
2	15	11