CODEWITHSUNDEEP
mysqlview
Fadly Dzil
Fadly Dzil

Reputation: 2206

Put sum() function into a view

I have normalize my table to separate beetwen tb_header and tb_detail. This is the tb_repair detail.

mysql> SELECT DETAIL_ID, REPAIR_ESTIMATE_ID, REMARKS, MANHOUR FROM tb_repair_detail;
+-----------+--------------------+----------------------+---------+
| DETAIL_ID | REPAIR_ESTIMATE_ID | REMARKS              | MANHOUR |
+-----------+--------------------+----------------------+---------+
|         9 |                 50 | Anda                 |   12.00 |
|        10 |                 50 | Jika                 |   10.00 |
|        11 |                 51 | ACRYLIC              |   12.00 |
|        12 |                 51 | Pembersihan exterior |   10.00 |
|        13 |                 51 | Repairing            |   10.00 |
+-----------+--------------------+----------------------+---------+
5 rows in set (0.00 sec)

Now, for more readable to user, now I want to create a view that would be like this :

mysql> SELECT  a.REPAIR_ESTIMATE_ID , a.EIR_REF  ,
->  (SELECT b.NAMA FROM tb_customer_master b WHERE a.COSTUMER_ID = b.COSTUMER_ID) AS "NAMA_CUSTOMER"
-> from tb_master_repair_estimate a ;
+--------------------+---------+----------------------+
| REPAIR_ESTIMATE_ID | EIR_REF | NAMA_CUSTOMER        |
+--------------------+---------+----------------------+
|                 50 | 1545053 | APL                  |
|                 51 | 1545052 | APL                  |
+--------------------+---------+----------------------+
2 rows in set (0.00 sec)

My question is, I want to put the manhour total based REPAIR_ESTIMATE_ID How to put this manhour total in this view ? I know the query to sum() each REPAIR ESTIMATE_ID like

mysql> SELECT SUM(MANHOUR) AS TOTAL FROM tb_repair_detail a WHERE a.REPAIR_ESTIMATE_ID = 51

mysql> SELECT SUM(MANHOUR) AS TOTAL FROM tb_repair_detail a WHERE a.REPAIR_ESTIMATE_ID = 50;
+-------+
| TOTAL |
+-------+
| 22.00 |
+-------+

+-------+
| TOTAL |
+-------+
| 32.00 |
+-------+
1 row in set (0.00 sec)

But, how I get them into those view ?

Upvotes: 0

Views: 27

Answers (1)

Darwin von Corax
Darwin von Corax

Reputation: 5246

SELECT a.repair_estimate_id, a.eir_ref, b.nama AS nama_customer, c.total
  FROM tb_master_repair_estimate a, tb_customer_master b,
    (SELECT repair_estimate_id, SUM(manhour) AS total
      FROM tb_repair_detail
      GROUP BY repair_estimate_id) c
  WHERE a.repair_estimate_id = c.repair_estimate_id
    AND a.COSTUMER_ID = b.COSTUMER_ID

should, I think, give the desired result set. Make that your view's query and you ought to be set.

Note the clause GROUP BY repair_estimate_id which causes the subquery to compute SUM(manhour) for each distinct value of repair_estimate_id.

Upvotes: 1

Related Questions