Search code examples
mysqlsqldatabaseunionrollup

union and rollup in mysql

I couldn't find what is causing this error, when I union headers with the actual data constructed using rollup to write them to a .csv file.

CREATE TABLE `products` (
`id` int(11) default NULL,
`item` varchar(100) default NULL,
`value` int(11) default NULL
) ENGINE=MyISAM ;

INSERT INTO `products` VALUES (1,'Mobiles', '1000'),(5,'Mobiles', '2000'),(8,'Mobiles', 4000),(18,'Books',100),(28,'Books', 200),(28,'Books',400);

When I try the below query,

 SELECT * FROM (
    (SELECT 'ITEM', 'SUM')
    UNION
    (select item, sum(value) from products group by item with rollup)
) data;

I get this error

ERROR 1221 (HY000): Incorrect usage of CUBE/ROLLUP and ORDER BY

Thanks in advance.

Solution 
  • select 'ITEM', 'SUM'
union
select item, sum(value) from products group by item with rollup
;

    Result:

    +---------+------+
| ITEM    | SUM  |
+---------+------+
| ITEM    | SUM  |
| Books   | 700  |
| Mobiles | 7000 |
| NULL    | 7700 |
+---------+------+