Select SUM with Grouping MYSQL

Question

I'm having difficulties to SUM in mysql with grouping from certain field

+------------+----------+----------------------+
| order_plan | subgroup | requested_amount_usd |
+------------+----------+----------------------+
| 2020-07    | SOFTWARE |                10.00 |
| 2020-07    | SOFTWARE |                15.00 |
| 2020-07    | FLASHDISK|                25.00 |
| 2020-08    | SOFTWARE |                10.00 |
+------------+----------+----------------------+

what i've tried to query (using CI) :

$this->db
->distinct('subgroup','order_plan')
->select_sum('requested_amount_usd','requested_amount')
->from($table);
return $this->db->get()->result_array();

the result was looks like this :

+------------------+
| requested_amount |
+------------------+
|           60.00  |
+------------------+

while I was expecting the result would be like this :

+------------+----------+----------------------+
| order_plan | subgroup | requested_amount_usd |
+------------+----------+----------------------+
| 2020-07    | SOFTWARE |                25.00 |
| 2020-07    | FLASHDISK|                25.00 |
| 2020-08    | SOFTWARE |                10.00 |
+------------+----------+----------------------+

How can I achieve that ?

Strawberry · Accepted Answer · 2020-07-12 09:18:38Z

1


SELECT order_plan
     , subgroup
     , SUM(requested_amount_usd) AS requested_amount_usd
  FROM {table} 
 group 
    by subgroup
     , order_plan

Always remember you need to GROUP all the other columns while using SUM(),AVG(),COUNT()

edited Jul 12, 2020 at 9:18

Strawberry

34k14 gold badges43 silver badges57 bronze badges

answered Jul 12, 2020 at 4:16

Raj

194 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

owf Over a year ago

i tried this one then i got this error --> ERROR 1241 (21000): Operand should contain 1 column(s), what should i do ?

Raj Over a year ago

Try Removing brackets from group by, If it works close the question by marking it answered.

Strawberry Over a year ago

@owf there's no multi-column operand here. Perhaps you have some kind of trigger or something

Collectives™ on Stack Overflow

Select SUM with Grouping MYSQL

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related