Search code examples
mysqlaggregate-functionsrollup

Mysql: create procedure with various selects through distinct value cycle

I've got a table like that 

TIME  |STRCOUNTRY |STROPERATOR | NINCOMINGCALLS
300718|RUSSIA     |MTS         | 2
300718|RUSSIA     |Megafon     | 3
300718|UK         |Vodafone    | 1
300718|UK         |UKTele      | 3

And it the end I want to show: calls for operator, sum for all operators by country

RUSSIA  |Megafon |  3
RUSSIA  |MTS     |  2
RUSSIA  |#       |  5
UK      |Vodafone|  1
UK      |UKTele  |  3
UK      |#       |  4
#       | #      | 9

Which can be received with queries like this

select * from TM_COMMON_STAT where strCountry = "RUSSIA" and TIME = "300718"

UNION


select STRCOUNTRY, '#' as STROPERATOR, SUM(NINCOMINGCALLS) as 'NINCOMINGCALLS' from TM_COMMON_STAT where strCountry = "RUSSIA" and TIME = "300718"

How can I get output for all operators in the table (distinct STRCOUNTRY + STROPERATOR) and get a final sum for all countries (where operator = #) in the end?

Thanks for the help.

Solution

  • This is a job for GROUP BY...WITH ROLLUP.

    Try this

    SELECT STRCOUNTRY, STROPERATOR, SUM(NINCOMINGCALLS) NINCOMINGCALLS
  FROM TM_COMMON_STAT
 WHERE <<<whatever filters you require >>>
 GROUP BY STRCOUNTRY, STROPERATOR WITH ROLLUP