I tried to total up the claim amount in summary table based on type of claim.
I mean that one row for each employee but there are 3 column which is "Total vehicle claim", "total medical claim" and total others claim".
How do I do that?
EDIT (from OP's comment):
$query="SELECT c.empid,u.userName, SUM(c_amount) AS total vehicle claim
FROM claim c JOIN
user u
ON (c.empid = u.empid)
WHERE c_type = 'Motorcycle' OR c_type='Car'
GROUP by empid"
This can be done by creating two views. A view is a virtual table. This allows you to create a virtual table with the empid and the SUM(c_amount) for vehicle claims and another virtual table for the sum of medical claims.
The views will be:
CREATE VIEW vehicle_claim AS
SELECT empid, SUM(c_amount) AS 'total vehicle claim'
FROM claim
WHERE c_type IN('Motorcycle', 'Car')
GROUP BY empid;
CREATE VIEW medical_claim AS
SELECT empid, SUM(c_amount) AS 'total medical claim'
FROM claim
WHERE c_type = 'Medical'
GROUP BY empid;
With the select statement:
SELECT *
FROM user u LEFT JOIN vehicle_claim v on u.empid = v.empid
LEFT JOIN medical_claim m ON u.empid = m.empid
In this query, I am selecting ALL users and I LEFT JOIN my two views. I do this so you will get data even if a user does not have any (vehical/medical)claim.