CODEWITHSUNDEEP
mysql
Mango
Mango

Reputation: 79

MySQL SUM with multiple column group separately

enter image description here

Here is the query have tried:

SELECT Actor, Country, Customer_Name, SUM(Revenue), (Employee_Number) 
FROM CUSTOMER 
GROUP BY Actor, Country;

But the outcome is not success.

How to do this and is it possible to get the end result in one query?

Upvotes: 1

Views: 501

Answers (2)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

Use with rollup:

select t1.a, t1.b, t1.c, t2.id
from (select a, b, sum(c) c from t
      group by a, b
      with rollup) t1
left join t t2 on t1.a = t2.a and t1.b = t2.b
order by case when t1.a is null then 1 else 0 end, 
         t1.a, 
         case when t1.b is null then 1 else 0 end, 
         t1.b

Fiddle http://sqlfiddle.com/#!9/14df56/5

You can easily convert to your column names as:

a -> Actor
b -> Country
c -> Revenue
id -> Costomer_Name, Employee_Number

Upvotes: 1

shA.t
shA.t

Reputation: 16958

I think you can use UNION ALL like this:

SELECT 
    Actor, Country, Customer_Name, Revenue, Employee_Number
FROM (
    SELECT Actor, Country, Customer_Name, SUM(Revenue) As Revenue, Employee_Number, Actor + Country + Customer_Name AS ord
    FROM CUSTOMER
    GROUP BY Actor, Country, Customer_Name, Employee_Number
    UNION ALL
    SELECT Actor, Country, 'SUM' AS Customer_Name, SUM(Revenue) As Revenue, SUM(Employee_Number) AS Employee_Number, Actor + Country + 'zzzzz' AS ord
    FROM CUSTOMER
    GROUP BY Actor, Country
    UNION ALL
    SELECT Actor, 'SUM' AS Country, 'SUM' AS Customer_Name, SUM(Revenue) As Revenue, SUM(Employee_Number) AS Employee_Number, Actor + 'zzzzzzzzzz' AS ord
    FROM CUSTOMER
    GROUP BY Actor) t
ORDER BY
    ord

For this:

Actor   | Country   | Customer_Name | Revenue   | Employee_Number
--------+-----------+---------------+-----------+-------------------
user1   | AUS       | qsd corp      | 60        | 20
user1   | AUS       | xyz corp      | 50        | 5
user1   | AUS       | SUM           | 110       | 25
user1   | US        | abc corp      | 100       | 6
user1   | US        | efg corp      | 200       | 10
user1   | US        | SUM           | 300       | 16
user1   | SUM       | SUM           | 410       | 41

Upvotes: 2

Related Questions