Mysql – Multi-table sum of cost breakdown by pricing category

group byMySQLsum

Looking for help with a query to get the sum of the subtotals for each price category from a list of line items, by order. Scratching my head on the group by stuff.

There are 3 categories: setup, build, and finishing. I'd like to get a total for each category per order. Testing shows my sql foo is rusty and looking for guidance on how to roll this stuff up. I'm already querying a view to make this simpler.

http://sqlfiddle.com/#!9/acb02/3

CREATE TABLE `OrderLineItems` (
  `orderId` INT(11) NOT NULL,
  `dt` DATETIME,
  `buyerEmail` VARCHAR(512),
  `orgId` INT(11) NOT NULL,
  `description` VARCHAR(512),
  `application` VARCHAR(512),
  `category` VARCHAR(30),
  `quantity` INT(5),
  `price` DECIMAL(9,2),
  `subtotal` DECIMAL(19,2)
)

Looking for one row per order in the result, with the total for each price category. A total cost for the order would be bonus.

 Order Id | Setup Total | Build Total | Finishing Total | All Total
--------------------------------------------------------------------

Also, adding in the date, is it possible to get a grouping by month for the 3 categories?

 Month | Setup Tot | Build Tot | Finishing Tot | All Tot
---------------------------------------------------------
 1/16 | 
 2/16 |

Best Answer

Unfortunately, MySQL is lacking window or analytic functions which make complex grouping queries easier. For your use case, though, it should be possible to get it working with a single query.

Doing a simple row-based group of your sample dataset:

MySQL [test]> select orderId , category, count(*), sum(subtotal) from OrderLineItems group by 1,2;
+---------+-----------+----------+---------------+
| orderId | category  | count(*) | sum(subtotal) |
+---------+-----------+----------+---------------+
|  323607 | BUILD     |        5 |       2515.00 |
|  323607 | FINISHING |        1 |         30.00 |
|  323607 | SETUP     |        2 |         95.00 |
|  323614 | BUILD     |        4 |       4614.00 |
|  323614 | SETUP     |        1 |         36.00 |
|  323636 | BUILD     |        2 |        840.00 |
|  323636 | FINISHING |        1 |        130.00 |
|  323636 | SETUP     |        7 |       1152.00 |
|  323649 | BUILD     |        1 |        240.00 |
|  323649 | FINISHING |        1 |         30.00 |
|  323649 | SETUP     |        4 |       1995.00 |
+---------+-----------+----------+---------------+
11 rows in set (0.00 sec)

You can pivot the subtotals to columns using the trick of using an IF statement in the select. This way you get the main part of your data with only one query:

SELECT orderId , count(*), 
sum(if(category='BUILD', subtotal, 0)) as Build_Total,
sum(if(category='SETUP', subtotal, 0)) as Setup_Total,
sum(if(category='FINISHING', subtotal, 0)) as Finishing_Total,
sum(subtotal) as Total
from OrderLineItems
GROUP BY 1;

With output:

+---------+----------+-------------+-------------+-----------------+---------+
| orderId | count(*) | Build_Total | Setup_Total | Finishing_Total | Total   |
+---------+----------+-------------+-------------+-----------------+---------+
|  323607 |        8 |     2515.00 |       95.00 |           30.00 | 2640.00 |
|  323614 |        5 |     4614.00 |       36.00 |            0.00 | 4650.00 |
|  323636 |       10 |      840.00 |     1152.00 |          130.00 | 2122.00 |
|  323649 |        6 |      240.00 |     1995.00 |           30.00 | 2265.00 |
+---------+----------+-------------+-------------+-----------------+---------+
4 rows in set (0.00 sec)

You can use the same approach to get your date-based rollups.

Related Question