MySQL 1241 error

Question

I have 2 independent queries which are calculating the total IN/OUT of payments for a given festival. The IN/OUT is given by a ENUM value (see below).

If I run the queries independently it gives the correct output of SUM based on the selected payment_type IN or OUT. My problem is when I try to combine them in one query to have 2 separate columns for IN/OUT like below.

I have error in MySQL "Operand should contain 1 column(s)". After I've done some research I believe is the subqueries are wrong but I'm not quite sure how to solve it.

Thanks in advance for any help...

TOTAL IN

SELECT
    SUM(`payment`.`pmt_amount`) AS `TOTAL IN`
    , `payment`.`pmt_type`
    , `festival`.`id_festival`
FROM
    payment
    INNER JOIN festival
        ON (`payment`.`id_festival` = `festival`.`id_festival`)
WHERE (`payment`.`pmt_type` LIKE '%IN'
    AND `festival`.`id_festival` = 1);

And TOTAL OUT

SELECT
    SUM(`payment`.`pmt_amount`) AS `TOTAL OUT`
    , `payment`.`pmt_type`
    , `festival`.`id_festival`
FROM
    payment
    INNER JOIN festival
        ON (`payment`.`id_festival` = `festival`.`id_festival`)
WHERE (`payment`.`pmt_type` LIKE '%OUT'
    AND `festival`.`id_festival` = 1);

Combined

SELECT
  festival.id_festival,
  payment.pmt_amount,
  payment.pmt_type,
  (SELECT
      payment.pmt_type,
      SUM(payment.pmt_amount) AS `TOTAL OUT`
    FROM payment
    WHERE payment.pmt_type LIKE '%OUT'),
  (SELECT
      payment.pmt_type,
      SUM(payment.pmt_amount) AS `TOTAL IN`
    FROM payment
    WHERE payment.pmt_type LIKE '%IN')
FROM payment
  INNER JOIN festival
    ON payment.pmt_amount = festival.id_festival
WHERE festival.id_festival = 1

score 1 · Answer 1 · answered Apr 07 '18 at 10:34

Your subquery must return only one column but you query is returning two column which is wrong try below mention query:

SELECT
  festival.id_festival,
  payment.pmt_amount,
  payment.pmt_type,
  (SELECT
      SUM(payment.pmt_amount) AS `TOTAL OUT`
    FROM payment
    WHERE payment.pmt_type LIKE '%OUT') AS `TOTAL OUT`,
  (SELECT
      SUM(payment.pmt_amount) AS `TOTAL IN`
    FROM payment
    WHERE payment.pmt_type LIKE '%IN') AS `TOTAL IN`
FROM payment
  INNER JOIN festival
    ON payment.pmt_amount = festival.id_festival
WHERE festival.id_festival = 1

score 1 · Accepted Answer · answered Apr 07 '18 at 10:40

You probably just want to use conditional aggregation here:

SELECT
    f.id_festival,
    SUM(CASE WHEN p.pmt_type = 'Payment IN'  THEN p.pmt_amount ELSE 0 END) AS `TOTAL IN`,
    SUM(CASE WHEN p.pmt_type = 'Payment OUT' THEN p.pmt_amount ELSE 0 END) AS `TOTAL OUT`
FROM festival f
INNER JOIN payment p
    ON p.id_festival = f.id_festival
WHERE f.id_festival = 1
GROUP BY
    f.id_festival;

Note that your query is only looking at one festival, but the correct way to express this is via GROUP BY even if we only want to retain a single group in the output.

MySQL 1241 error

2 Answers2