Mysql – Using GROUPing or ROLLUP rather than SUMing every column

group byMySQLsum

I am currently looking for a way to achieve what this query is doing without using SUM for every column and, if possible, GROUPing or using ROLLUP or something similar.

This query gives me a total for orders across all currencies.

SELECT DATE, TYPE, SUM(orders), SUM(qty),
SUM(shipped_orders), SUM(shipped_qty), SUM(return_qty),
SUM(shipped_minus_returns_units)
    FROM daily_report 
    WHERE `date` >= '2015-07-01' AND `date` <= '2015-07-29'
GROUP BY DATE ASC, TYPE ASC;

My current query looks like this:

SELECT DATE, currency, TYPE, orders, qty, shipped_orders, shipped_qty, return_qty,
shipped_minus_returns_units
    FROM daily_report 
    WHERE `date` >= '2015-07-01' AND `date` <= '2015-07-29'
GROUP BY DATE ASC, TYPE ASC, currency ASC;

which returns this DB table view:

Daily Report DB View

CREATE TABLE `daily_report` (
  `date` date NOT NULL,
  `currency` varchar(5) NOT NULL,
  `type` enum('customer','exchanges') NOT NULL,
  `orders` int(11) NOT NULL DEFAULT '0',
  `qty` int(11) NOT NULL DEFAULT '0',
  `value` decimal(9,2) NOT NULL DEFAULT '0.00',
  `value_ex_ship` decimal(9,2) NOT NULL DEFAULT '0.00',
  `average_order_value` decimal(7,2) NOT NULL,
  `average_item_value` decimal(7,2) NOT NULL,
  `average_item_per_order` decimal(7,2) NOT NULL,
  `shipped_orders` int(11) NOT NULL DEFAULT '0',
  `shipped_qty` int(11) NOT NULL DEFAULT '0',
  `shipped_value` decimal(9,2) NOT NULL DEFAULT '0.00',
  `net_shipped_value` decimal(9,2) NOT NULL DEFAULT '0.00',
  `return_qty` int(11) NOT NULL,
  `return_value` decimal(9,2) NOT NULL DEFAULT '0.00',
  `returns_to_shipped` decimal(9,2) NOT NULL DEFAULT '0.00',
  `shipped_minus_returns_units` int(11) NOT NULL DEFAULT '0',
  `shipped_minus_returns_value` decimal(9,2) NOT NULL DEFAULT '0.00',
  PRIMARY KEY (`date`,`currency`,`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Is there a way to total up these columns using group rather than SUM'ing everything?

Best Answer

If I understand correctly, the ideal solution would need GROUP BY GROUPING SETS which is not available in MySQL.

Your two options are to use

  • WITH ROLLUP:

    SELECT date, type, currency, 
       SUM(orders) AS orders, SUM(qty) AS qty, 
       SUM(shipped_orders) AS shipped_orders, 
       SUM(shipped_qty) AS shipped_qty, 
       SUM(return_qty) AS return_qty,
       SUM(shipped_minus_returns_units) AS shipped_minus_returns_units
FROM daily_report 
WHERE date >= '2015-07-01' AND date <= '2015-07-29'
GROUP BY date, type, currency
  WITH ROLLUP ;

  • two queries, one simple and one with the aggregations needed and UNION ALL:

    SELECT date, type, currency, 
       orders, qty, shipped_orders, 
       shipped_qty, return_qty, shipped_minus_returns_units
FROM daily_report 
WHERE date >= '2015-07-01' AND date <= '2015-07-29'

UNION ALL

SELECT date, type, NULL, 
       SUM(orders), SUM(qty), SUM(shipped_orders),
       SUM(shipped_qty), SUM(return_qty), SUM(shipped_minus_returns_units)
FROM daily_report 
WHERE date >= '2015-07-01' AND date <= '2015-07-29'
GROUP BY date, type 

ORDER BY date, type, currency ;

The first option will have more groupings than you need (per date only and a single total in the end).

Related Question