CODEWITHSUNDEEP
mysqlsql
firdaus nanda
firdaus nanda

Reputation: 51

how can I calculate from two tables in mysql

I have 2 tables bellow

0 --> Pending
1 --> Success
2 --> Fail

table : mntnc
+-------+-------+-------+
|   id  |   own |   sts |
+-------+-------+-------+
|   1   |   BN  |   1   |
|   2   |   BB  |   2   |
|   3   |   BN  |   1   |
|   4   |   BD  |   1   |
|   5   |   BD  |   0   |

table : istlsi
+-------+-------+-------+
|   id  |   own |   sts |
+-------+-------+-------+
|   1   |   BN  |   1   |
|   2   |   BB  |   1   |
|   3   |   BB  |   1   |
|   4   |   BC  |   0   |
|   5   |   BD  |   2   |

of the two tables above, I want to add both of them to be the table below

+-------+-----------+-----------+-----------+
|   own |   success |   fail    |   pending |
+-------+-----------+-----------+-----------+
|   BN  |       3   |       0   |       0   |
|   BB  |       2   |       1   |       0   |
|   BD  |       1   |       1   |       1   |
|   BC  |       0   |       0   |       1   |

Upvotes: 0

Views: 47

Answers (1)

xQbert
xQbert

Reputation: 35323

The two key points here:

  1. Union tables (I aliased result to B)
  2. Use sum(case...) for each column.

First we union both tables together as an inline view.

We then use a case statement for each desired column and evaluate the status setting the value to 1 or 0 depending on sts value. and then sum those...

SELECT own
     , sum(case when sts=1 then 1 else 0 end) as Success
     , sum(case when sts=2 then 1 else 0 end) as Fail
     , sum(case when sts=0 then 1 else 0 end) as Pending
FROM ( SELECT ID, own, sts
       FROM mntnc
       UNION ALL
       SELECT id, own, sts 
       FROM istlsi
     ) B
GROUP BY own

Upvotes: 3

Related Questions