0

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?

  • I'd keep the first query but with GROUP BY DATE ASC, TYPE ASC, currency ASC ;. The 2nd query makes no sense. It's even invalid SQL, try it with ONLY_FULL_GROUP_BY setting on. – ypercubeᵀᴹ Jul 29 '15 at 9:55
  • @ypercube Thanks for your comment. So maybe my best bet is to stick with the SUMing then? My first query works it's just I thought maybe some clever grouping rather than SUMing every column would be the more efficient way to do this. I don't actually need the currency ASC in the first query at all. Out of interest what is invalid about the second query? – crmpicco Jul 29 '15 at 10:00
  • First, I think you need the currency in the group by. I can't figure how adding 100 euros with 100 dollars will make 200 (of what?) a sensible result. If the columns used in the query do not have money-data but only orders/quantities, then it's fine. – ypercubeᵀᴹ Jul 29 '15 at 10:04
  • For the 2nd query, the not-sensible thing is that you have columsn in the SELECT list (orders, quantity, etc.), that are not in the GROUP BY list and are not aggregated. For any combination of date, type, currency you'll have many rows but the group by will "collapse"/gather them in one row in the result. These (many) rows will have many orders, quantity, etc. values but only one will (arbitrarily) chosen and displayed in the result. – ypercubeᵀᴹ Jul 29 '15 at 10:05
  • It would also help if you added the SHOW CREATE TABLE daily_report; output in the question. Some of my comments depend on the unique constraints that you have on the table. – ypercubeᵀᴹ Jul 29 '15 at 10:08
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.