Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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|
- ----------------------------
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement