I am trying to get the order_payment_total of the unique od_grp_id once but while using sum it get added.

CREATE TABLE IF NOT EXISTS `subscription` (
  `id` int(11) unsigned NOT NULL, 
  `od_grp_id` int(11) unsigned NULL, 
  `user_id` int(11) NOT NULL, 
  `order_discount` decimal(10, 2) null, 
  PRIMARY KEY (`id`)
) DEFAULT CHARSET = utf8;

INSERT INTO `subscription` (
  `id`, `od_grp_id`, `user_id`, `order_discount`
) 
VALUES 
  (123994, NULL, 115, null), 
  (124255, NULL, 115, null), 
  (124703, 1647692222, 115, null), 
  (125788, 1647692312, 115, '25.00'), 
  (125789, 1647692312, 115, '5.00');

CREATE TABLE IF NOT EXISTS `online_payment_against_subscription` (
  `subscription_od_grp_id` int(11) unsigned NOT NULL, 
  `order_payment_total` decimal(10, 2) unsigned NOT NULL, 
  `user_id` int(11) NOT NULL
) DEFAULT CHARSET = utf8;

INSERT INTO `online_payment_against_subscription` (
  `subscription_od_grp_id`, `order_payment_total`, `user_id` 
) 
VALUES 
  (1643695200, '45.00', 115), 
  (1647692312, '250.00', 115), 
  (1647692222, '30.00', 115);

SELECT 
  sum(y.order_payment_total), 
  sum(s.order_discount) 
FROM 
  subscription s 
  LEFT JOIN(
    SELECT 
      SUM(order_payment_total) as order_payment_total, 
      user_id, 
      subscription_od_grp_id 
    FROM 
      online_payment_against_subscription 
    GROUP BY 
      subscription_od_grp_id
  ) y ON y.subscription_od_grp_id = s.od_grp_id 
WHERE 
  find_in_set(
    s.id, '123994,124255,124703,125788,125789'
  ) 
group by 
  s.user_id

Current Output:
| sum(y.order_payment_total) |sum(s.order_discount)  |
|----------------------------|-----------------------|
|                        530 |                    30 |


Expected Ouput:
| sum(y.order_payment_total) |sum(s.order_discount)  |
|----------------------------|-----------------------|
|                       280  |                    30 |

Sql Fiddle: http://sqlfiddle.com/#!9/5628f5/1

CodePudding user response：

If I understand correctly, The problem is caused by some duplicate od_grp_id from subscription table, so you might remove the duplicate od_grp_id before JOIN, so we might do that in a subquery.

Query 1:

SELECT 
   SUM(order_payment_total),
   SUM(order_discount)
FROM (
   SELECT od_grp_id,SUM(order_discount) order_discount
   FROM subscription
   WHERE find_in_set(id, '123994,124255,124703,125788,125789') 
   GROUP BY od_grp_id
 ) s 
LEFT JOIN online_payment_against_subscription y ON y.subscription_od_grp_id=s.od_grp_id

Results:

| SUM(order_payment_total) | SUM(order_discount) |
|--------------------------|---------------------|
|                      280 |                  30 |

CodePudding user response：

I think you are getting this error because every subscription doesn't have an order payment that is you are getting NULL values.

You can try to remove them by using this -

SELECT y.order_payment_total
    FROM subscription s
             LEFT JOIN(SELECT SUM(order_payment_total) AS order_payment_total, user_id, subscription_od_grp_id
                       FROM online_payment_against_subscription
                       GROUP BY subscription_od_grp_id) y ON y.subscription_od_grp_id = s.od_grp_id
    WHERE FIND_IN_SET(s.id, '11258,22547,18586')
      AND y.order_payment_total IS NOT NULL;

Or you can make NULL values 0 if you required -

SELECT COALESCE(y.order_payment_total, 0) AS order_payment_total
    FROM subscription s
             LEFT JOIN(SELECT SUM(order_payment_total) AS order_payment_total, user_id, subscription_od_grp_id
                       FROM online_payment_against_subscription
                       GROUP BY subscription_od_grp_id) y ON y.subscription_od_grp_id = s.od_grp_id
    WHERE FIND_IN_SET(s.id, '11258,22547,18586');