matrix/crosstab/pivot query in mySQL

Question

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 ServiceNames. 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.

Answer 1

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;

Answer 2

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 |
+--------+---------+--------------+-------+------+------------+