CODEWITHSUNDEEP
mysqlsqldatabaseunion
adirocks27
adirocks27

Reputation: 141

How to update table2 from table1, into corresponding entry in table2?

I want to count the occurrences of banks in different tables, and update in corresponding columns of FinalTable.

BankTable:

  BankName  ErrorCode
    SBI         200
    ICICI       200
    SBI         400
    SBI         500
    SBI         503

ATMTable:

BankName    ErrorCode
ICICI       200
ICICI       400
ICICI       400
SBI         500
SBI         503

MitraTable:

BankName    ErrorCode
ICICI        200
ICICI        200
SBI          400
SBI          500
SBI          503
PNB          501

FinalTable:

BankName    BankCount   ATMCount    MitraCount  TotalCount
ICICI            1         3           2          6
SBI              4         2           3          9
PNB              0         0           1          1

I want to count the occurances of different banks and set the values in FinalTable.

Upvotes: 0

Views: 49

Answers (2)

Saharsh Shah
Saharsh Shah

Reputation: 29071

Try this: 

SELECT BankName, 
       SUM(BankCount) AS BankCount, 
       SUM(ATMCount) AS ATMCount, 
       SUM(MitraCount) AS MitraCount, 
       SUM(BankCount + ATMCount + MitraCount) AS TotalCount 
FROM (SELECT BankName, 
             COUNT(ErrorCode) AS BankCount, 
             0 AS ATMCount, 
             0 AS MitraCount 
      FROM BankTable 
      GROUP BY BankName 
     UNION 
      SELECT BankName, 
             0 AS BankCount, 
             COUNT(ErrorCode) AS ATMCount, 
             0 AS MitraCount 
      FROM ATMTable 
      GROUP BY BankName 
     UNION 
      SELECT BankName, 
             0 AS BankCount, 
             0 AS ATMCount, 
             COUNT(ErrorCode) AS MitraCount 
      FROM MitraTable 
      GROUP BY BankName
    ) AS A
GROUP BY BankName;

Upvotes: 0

Giorgos Betsos
Giorgos Betsos

Reputation: 72205

You can use UNION ALL together with conditional aggregation:

SELECT BankName,
       COUNT(CASE WHEN src = 'Bank' THEN 1 END) AS BankCount,
       COUNT(CASE WHEN src = 'ATM' THEN 1 END) AS ATMCount,
       COUNT(CASE WHEN src = 'Mitra' THEN 1 END) AS MitraCount,
       COUNT(*) AS TotalCount

FROM (
   SELECT BankName, 'Bank' AS src 
   FROM BankTable

   UNION ALL 

   SELECT BankName, 'ATM' AS src
   FROM ATMTable

   UNION ALL

   SELECT BankName, 'Mitra' AS src
   FROM MitraTable) AS t
GROUP BY BankName

Field src is used to return the source table to the outer query consuming the results of the UNION ALL operations. Using this field we can perform conditional aggregation in order to count the occurence of each bank in each of the source tables.

Upvotes: 1

Related Questions