Read 348 times | Created 2014-05-05 04:17:37 | Updated 2014-05-05 05:32:27 | | |

 

DROP TABLE IF EXISTS `equipment`;
CREATE TABLE IF NOT EXISTS `equipment` (
 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 `equipment` VARCHAR(10) NOT NULL,
 `type` CHAR(1) NOT NULL,
 `qty` int(11) unsigned NOT NULL,
 PRIMARY KEY(`id`)
)ENGINE='MyISAM';

INSERT INTO `equipment`(`equipment`,`type`,`qty`) VALUES
('equip_1','A',10),
('equip_1','B',10),
('equip_2','A',8),
('equip_2','B',10),
('equip_3','A',10),
('equip_3','B',10);


SELECT 
IF(COALESCE(type,'Total')='Total','',COALESCE(equipment,'GrandTotal')) AS Equipment,
IF(COALESCE(equipment,'GrandTotal')='GrandTotal','GrandTotal:',COALESCE(type,'Total:')) AS Type,
SUM(qty) AS Qty
FROM equipment
GROUP BY equipment,type WITH ROLLUP;

----------------------------
|Equipment | Type      |Qty|
----------------------------
|equip_1   |     A     | 10|
|equip_1   |     B     | 10|
|          |  Total:   | 20|
|equip_2   |     A     |  8|
|equip_2   |     B     | 10|
|          |  Total:   | 18|
|equip_3   |     A     | 10|
|equip_3   |     B     | 10|
|          |  Total:   | 20|
|          |GrandTotal:| 58|
----------------------------

SELECT 
IF(d.equipment IS NULL,'',d.equipment)  AS Equipment,
IF(COALESCE(a.equipment,'GrandTotal')='GrandTotal','GrandTotal:',COALESCE(a.type,'Total:')) AS Type,
SUM(a.qty) AS Qty
FROM equipment a
LEFT JOIN (
  SELECT  b. * 
  FROM equipment b
  LEFT JOIN equipment c ON ( 
    b.equipment = c.equipment
    AND b.id > c.id ) 
  WHERE c.id IS NULL
)d ON (d.equipment=a.equipment AND d.type=a.type)
GROUP BY a.equipment,a.type WITH ROLLUP;

----------------------------
|Equipment | Type      |Qty|
----------------------------
|equip_1   |     A     | 10|
|          |     B     | 10|
|          |  Total:   | 20|
|equip_2   |     A     |  8|
|          |     B     | 10|
|          |  Total:   | 18|
|equip_3   |     A     | 10|
|          |     B     | 10|
|          |  Total:   | 20|
|          |GrandTotal:| 58|
----------------------------