matrix/crosstab/pivot query in mySQL
I have a Bill table like this
BillNo - 1, ServiceCode -1, ServiceName - 'Lab test' , ItemAmount -30
BillNo -1, ServiceCode -2, ServiceName -'Consultation', ItemAmount -70
I need output like this
Bill No -1, Total Bill Amount-100 , Lab Test-30, Consultation-70, Drugs-0, Misc-0
So basically i need the Bill Amount which is the SUM of all ItemAmount Grouped by BillNo. Then in the same row i need to distribute this Bill Amount to the 4 Servi开发者_C百科ceNames. So 100 gets distributed as 30 for Lab test and 70 for Consultation.
This gave me what i want but it is not optimal.
SELECT
BD.BILLNO AS BN,
SUM(BD.ITEMAMOUNT) AS "Bill Amount",
(SELECT SUM(BD.ITEMAMOUNT) FROM BILLDETAILS BD
WHERE SERVICENAME LIKE '%Lab%'
AND BD.BILLNO = BN ) AS "Lab",
(SELECT SUM(BD.ITEMAMOUNT) FROM BILLDETAILS BD
WHERE SERVICENAME LIKE '%Consult%'
AND BD.BILLNO = BN ) AS "Consultation",
(SELECT SUM(BD.ITEMAMOUNT) FROM BILLDETAILS BD
WHERE SERVICENAME LIKE '%Procedure%'
AND BD.BILLNO = BN ) AS "Procedures",
(SELECT SUM(BD.ITEMAMOUNT) FROM BILLDETAILS BD
WHERE SERVICENAME LIKE '%Drugs%'
AND BD.BILLNO = BN ) AS "Drugs",
(SELECT SUM(BD.ITEMAMOUNT) FROM BILLDETAILS BD
WHERE SERVICENAME NOT LIKE '%Lab%'
AND SERVICENAME NOT LIKE 'Consult%'
AND SERVICENAME NOT LIKE '%Procedure%'
AND SERVICENAME NOT LIKE '%Drugs%'
AND BD.BILLNO = BN )AS "Miscellaneous"
FROM BILLDETAILS BD
GROUP BY BD.BILLNO
How can i improve this mySQL query ?
Thanks. Chak.
Try this:
SELECT
BillNo,
LabTest,
Consultation,
Drugs,
Misc,
LabTest + Consultation + Drugs + Misc AS BillAmount
FROM (
SELECT
BillNo,
IFNULL(GROUP_CONCAT(IF(ServiceCode=1, ItemAmount, NULL)), 0) AS LabTest,
IFNULL(GROUP_CONCAT(IF(ServiceCode=2, ItemAmount, NULL)), 0) AS Consultation,
IFNULL(GROUP_CONCAT(IF(ServiceCode=3, ItemAmount, NULL)), 0) AS Drugs,
IFNULL(GROUP_CONCAT(IF(ServiceCode=4, ItemAmount, NULL)), 0) AS Misc
FROM bill
GROUP BY BillNo
) AS services;
I have tested ServiceCode
rather than ServiceName
, as I have assumed that the two are related. If they are not related, just change the comparison as necessary.
Test data:
INSERT INTO bill (BillNo, ServiceCode, ServiceName, ItemAmount) VALUES
(1, 1, 'Lab Test', 30),
(1, 2, 'Consultation', 70),
(2, 1, 'Lab Test', 40),
(2, 2, 'Consultation', 20),
(2, 3, 'Drugs', 15),
(2, 4, 'Misc', 25);
Results:
+--------+---------+--------------+-------+------+------------+
| BillNo | LabTest | Consultation | Drugs | Misc | BillAmount |
+--------+---------+--------------+-------+------+------------+
| 1 | 30 | 70 | 0 | 0 | 100 |
| 2 | 40 | 20 | 15 | 25 | 100 |
+--------+---------+--------------+-------+------+------------+
You can try this, though I havnt test it-
SELECT BillNo,count(ItemAmount) as "Total Bill Amount",
(CASE ServiceName WHEN 'Lab test' THEN count(ServiceName) as "Lab Test"
WHEN 'Consultation' THEN count(ServiceName) as Consultation
WHEN 'Drugs' THEN count(ServiceName) as Drugs
ELSE count(ServiceName) as Misc END CASE)
from Bill group by BillNo;
精彩评论