count based on conditions mysql 5.7

Question

i used mysql version 5.7, i have a table production for every product, amount, and using many expeditions like this

+---------+-----------------+------+--------+---------+
| Product | Type_Expedition | Pack | Amount |  Weight |
+---------+-----------------+------+--------+---------+
| Chicken | A               |    1 |      2 |       2 |
| Beef    | A               |    1 |      2 |       2 |
| Lamb    | B               |    1 |      2 |       2 |
| Beef    | B               |    2 |      2 |       4 |
| Chicken | A               |    3 |      2 |       6 |
| Lamb    | A               |    1 |      1 |       1 |
| Lamb    | A               |    1 |      1 |       1 |
+---------+-----------------+------+--------+---------+

how to calculate sum of weight and amount for type_expedition B and non-B (all type expedition except B) ?

i assume using this syntax (sorry i want to using dbfiddle.uk but it's error)

select product, type_expedition, pack, amount, weight, (sum(amount) where type_expedition = B), (sum(weight) where type_expedition = B) from my_table

expected results

+---------------------------------------------------+---+----+
|   Total amount and weight for type_expedition B   | 4 | 6  |
+---------------------------------------------------+---+----+
| Total amount and weight for type_expedition NON B | 8 | 12 |
+---------------------------------------------------+---+----+

forpas · Accepted Answer · 2020-04-15T08:56:19.930

You can use UNION ALL for the last 2 rows:

select t.Product, t.Type_Expedition, t.Pack, t.Amount, t.Weight
from (
  select *, 0 sort from my_table
  union all
  select 'Total Amount and Weight for expedition B', null, null,
    sum(amount),
    sum(weight), 1
  from my_table  
  where Type_Expedition = 'B'
  union all
  select 'Total Amount and Weight for expedition not B', null, null,
    sum(amount),
    sum(weight), 2
  from my_table 
  where Type_Expedition <> 'B'
) t
order by t.sort

See the demo.
Results:

| Product                                      | Type_Expedition | Pack | Amount | Weight |
| -------------------------------------------- | --------------- | ---- | ------ | ------ |
| Beef                                         | A               | 1    | 2      | 2      |
| Chicken                                      | A               | 3    | 2      | 6      |
| Lamb                                         | B               | 1    | 2      | 2      |
| Lamb                                         | A               | 1    | 1      | 1      |
| Chicken                                      | A               | 1    | 2      | 2      |
| Beef                                         | B               | 2    | 2      | 4      |
| Lamb                                         | A               | 1    | 1      | 1      |
| Total Amount and Weight for expedition B     |                 |      | 4      | 6      |
| Total Amount and Weight for expedition not B |                 |      | 8      | 12     |

If you want only the last 2 rows with the totals:

select 
  case Type_Expedition 
    when 'B' then 'Total Amount and Weight for expedition B'
    else 'Total Amount and Weight for expedition not B'
  end type,
  sum(amount),
  sum(weight)
from my_table
group by type

See the demo.
Results:

| Total Amount and Weight for expedition B     | 4           | 6           |
| Total Amount and Weight for expedition not B | 8           | 12          |

thankyou sir, let me applied to my real case and i give the update for you, in my real case, every coloumn comes from another table (like product from another table, and so does all of the coloumn) so on my real case it's containing many join table, what i want to ask is, better using your first solution or your 2nd solution for my real case sir? — 18Man, Apr 15 '20 at 08:59
They are not the same. The 2nd query returns only the totals. The 1st returns all the rows of the table too. It depends on what you want. — forpas, Apr 15 '20 at 09:01
so what makes difference for both queries is just a visual right? ok thankyou solved, have a nice day sir — 18Man, Apr 15 '20 at 09:03

count based on conditions mysql 5.7

1 Answers1