CODEWITHSUNDEEP
mysqlsqldatabase
jiangdongzi
jiangdongzi

Reputation: 393

Properly use join on a MySQL table

original table is as below

operation   processId   errcode
trade            1        200
trade            2        102
trade            3        200
trade            4        200
trade            5        107
trade            6        107
trade            7        200
trade            8        101
trade            9        102
trade           10        200
trade           11        107
bind            17        200
trade           12        200
bind            21        200
validate         4        200
validate         7        201
validate        10        201
validate        17        209
validate        21        207
validate        12        202
passset          1        307
passset          3        308

I want to get the result as below

operation   errcode num
trade         102   2
trade         107   3
trade         200   6
pass          200   1
pass          201   2
pass          202   1
pass          307   1
pass          308   2

the validate and passset will both be treated as the same operation name pass, their processId must be the same as trade, I think join is a must. I can use union and join for several times to get the result.

Can I get the result by an elegant way?

Upvotes: 1

Views: 51

Answers (1)

Radim Bača
Radim Bača

Reputation: 10711

Use CASE and GROUP BY

with data as
(
   select case when operation = 'validate' or operation = 'passset' 
          then 'pass' 
          else operation end operation,
          errcode
   from your_table
)
select operation, errcode, count(*)
from data
where operation = 'trade'
group by operation, errcode
union all
select d1.operation, d1.errcode, count(*)
from data d1
join data d2 on d1.processId = d2.processId
where d2.operation = 'trade' and d1.operation = 'pass'
group by d1.operation, d1.errcode

Upvotes: 3

Related Questions