Distinct unique value and sum others in mysql

Question

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

This seems likely to be a common error where people want some joins, each possibly involving a different key, of some subqueries, each possibly involving join and/or aggregation, but they erroneously try to do all the joining then all the aggregating or to aggregate over previous aggregations. Write separate aggregate subqueries over appropriate rows and/or aggregate (maybe distinct) case statements picking rows of (possibly join result) tables; then join the subqueries together. [Two SQL LEFT JOINS produce incorrect result](https://stackoverflow.com/q/12464037/3404097) — philipxy, Apr 03 '22 at 03:55
@D-Shih Why I am given ```sum(y.order_payment_total)``` means there is also another possibility that subscription have different group id eg: 1643695200 od_grp_id in subscription table means need to sum the total value i.e. 295. — Boopathi D, Apr 03 '22 at 05:49
A [mre] includes cut & paste & runnable code & example input; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. For SQL include DDL & tabular initialization code. For debug that includes the least code you can give that is code that you show is OK extended by code that you show is not OK. PS After you "chop to the 1st subexpression with unexpected result & say what you expected & why, justified by documentation" it will be a faq. PS Use `select *`. PS That isn't "minimal" input. PS Please clarify via edits, not comments. — philipxy, Apr 03 '22 at 05:51
"cut & paste & runnable code & example input" "For SQL include DDL & tabular initialization code." "chop to the 1st subexpression with unexpected result & say what you expected & why, justified by documentation" Etc etc etc. "Debugging fundamental." PS Your description of what you want is not clear. Use enough words, sentences & references to parts of examples to clearly & fully say what you mean. When giving a business relation(ship)/association or table (base or query result), say what a row in it states about the business situation in terms of its column values. — philipxy, Apr 03 '22 at 06:27
If you had given a clear explanation of your code & desiderata & expectations, you would presumably have noticed that you left out important relevant parts of your code that you just edited in. — philipxy, Apr 03 '22 at 08:06

score 0 · Answer 1 · answered Apr 03 '22 at 05:00

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');

Actually I need in a single row like ```order_payment_total 250```. Another scenario is if I had another od_grp_id 1643695200 has 45 order_payment_total. Then my order_payment_total can be 295. Need to take distinct value only once and then sum. — Boopathi D, Apr 03 '22 at 05:56

D-Shih · Accepted Answer · 2022-04-03T07:56:31.827

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 |

Distinct unique value and sum others in mysql

2 Answers2