Advanced MySQL Query (Group By and Sort)

Question

Please take a look at http://sqlfiddle.com/#!9/3a4d84/1/0

I have this table

CREATE TABLE IF NOT EXISTS `data` (
  `id` int(11) NOT NULL,
  `mdate` char(10) DEFAULT NULL,
  `customer_id` int(11) NOT NULL,
  `category` varchar(64) NOT NULL,
  `quantity` double NOT NULL,
  `sales` double NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `data` (`id`, `mdate`, `customer_id`, `category`, `quantity`, `sales`) VALUES
(2, '2015-02-01', 10720, 'Category1', 84.35, 894.24),
(3, '2015-01-02', 15570, 'Category1', 1000, 1325),
(4, '2015-01-02', 15570, 'Category6', 1000, 1325),
(5, '2015-01-05', 17090, 'Category1', 9600, 11671.76),
(6, '2015-01-05', 10360, 'Category2', 12110, 3981.6),
(7, '2015-01-05', 10360, 'Category1', 10150, 4828.95),
(8, '2015-01-06', 16460, 'Category3', 24000, 19656),
(9, '2015-01-05', 18260, 'Category3', 24000, 17688),
(10, '2015-01-05', 18260, 'Category2', 25200, 8129.02),
(11, '2015-01-05', 12570, 'Category1', 500, 6833.5),
(12, '2015-01-05', 11360, 'Category1', 2000, 1000),
(13, '2015-01-05', 11360, 'Category6', 23700, 8977.5),
(14, '2015-01-05', 15740, 'Category2', 26320, 9738.4),
(15, '2015-01-05', 10170, 'Category2', 24720, 9994.79),
(16, '2015-01-05', 12220, 'Category1', 4000, 2120),
(17, '2015-01-05', 13380, 'Category4', 24000, 44880),
(18, '2015-01-08', 13420, 'Category1', 23959, 23479.82),
(19, '2015-01-06', 10310, 'Category2', 24900, 13310.54),
(20, '2015-01-06', 17090, 'Category1', 6100, 2440),
(21, '2015-01-06', 17090, 'Category2', 2000, 1020),
(22, '2015-01-06', 17090, 'Category3', 2980, 1281.4),
(23, '2015-01-06', 17090, 'Category4', 2000, 1707.48),
(24, '2015-01-06', 10170, 'Category2', 25560, 12141),
(25, '2015-01-06', 13060, 'Category2', 24440, 9238.32),
(26, '2015-01-06', 10450, 'Category1', 3000, 2571.07),
(27, '2015-01-06', 11800, 'Category5', 9000, 163800),
(28, '2015-01-06', 18260, 'Category2', 25560, 8245.14),
(29, '2015-01-06', 10170, 'Category2', 25180, 11960.5),
(30, '2015-01-07', 10280, 'Category3', 21980, 16441.04),
(31, '2015-01-06', 19230, 'Category2', 17760, 9835.98),
(32, '2015-01-06', 19470, 'Category1', 6005, 13211),
(33, '2015-01-06', 18640, 'Category5', 1000, 18200),
(34, '2015-01-07', 10170, 'Category2', 26060, 12378.5),
(35, '2015-01-07', 18640, 'Category5', 1000, 18200),
(36, '2015-01-07', 13880, 'Category5', 500, 2303.93),
(37, '2015-01-07', 18260, 'Category2', 25360, 8180.63),
(38, '2015-01-05', 17040, 'Category2', 25080, 10784.4),
(39, '2015-01-02', 17040, 'Category2', 23340, 10036.2),
(40, '2015-01-02', 17040, 'Category3', 25500, 10965),
(41, '2015-01-02', 17040, 'Category7', 24960, 10732.8),
(42, '2015-01-08', 10720, 'Category1', 25000, 22375),
(43, '2015-01-07', 10680, 'Category1', 1000, 695),
(44, '2015-01-08', 16460, 'Category3', 24000, 20136),
(45, '2015-01-06', 10130, 'Category6', 500, 2950),
(46, '2015-01-08', 13880, 'Category2', 24000, 41280),
(47, '2015-01-07', 15180, 'Category3', 2000, 2500),
(48, '2015-01-06', 11060, 'Category3', 22000, 18480),
(49, '2015-01-06', 19450, 'Category6', 2000, 11400),
(50, '2015-01-07', 18150, 'Category4', 19748, 34756.48),
(51, '2015-01-06', 18690, 'Category1', 1000, 13128.98);

and when I use this query

SELECT 
  customer_id, 
  category, 
  sum(quantity) as quantity, 
  sum(sales) as sales 
FROM `data` 
GROUP BY customer_id, category

I get results like this

customer_id     category    quantity    sales
10130   Category6   500     2950
10170   Category2   101520  46474.79
10280   Category3   21980   16441.04
10310   Category2   24900   13310.54
10360   Category1   10150   4828.95
...

then with PHP, I build a table like this

      | Category1        | Category2        | Category3         
      | quantity | sales | quantity | sales | quantity | sales  ...
------+----------+-------+----------+-------+----------+--------------
10360 |  10150   | 4828  |          |       |          | 
10310 |          |       |  24900   | 13310 |          |
1890  |  80150   | 9828  |          |       |  84822   |  2310

So you can see in one row every category quanity and sales for one customer. But now I can't sort the tabel ... for example after "Category1 quantity".

I would like to build a mysql query to get this format ("Category1 quantity", "Category1 sales", "Category2 quantity", "Category2 sales"...) directly for each customer. So I can sort table with order by...

Is it possible? I am not quite sure how to realize it. Maybee I can find this Distinct() all categorys and build the query with php and many subselects ... or?

Thank you very mutch. Best Regards ben

Update, okey now I have this 'horror' query

SELECT 
  e.customer_id, 
  (SELECT SUM(s.quantity) FROM `data` as s WHERE s.category = 'Category1' AND s.customer_id = e.customer_id) as quantity1, 
  (SELECT SUM(s.sales)    FROM `data` as s WHERE s.category = 'Category1' AND s.customer_id = e.customer_id) as sales1,
  (SELECT SUM(s.quantity) FROM `data` as s WHERE s.category = 'Category2' AND s.customer_id = e.customer_id) as quantity2, 
  (SELECT SUM(s.sales)    FROM `data` as s WHERE s.category = 'Category2' AND s.customer_id = e.customer_id) as sales2,  
  (SELECT SUM(s.quantity) FROM `data` as s WHERE s.category = 'Category3' AND s.customer_id = e.customer_id) as quantity3, 
  (SELECT SUM(s.sales)    FROM `data` as s WHERE s.category = 'Category3' AND s.customer_id = e.customer_id) as sales3,
  (SELECT SUM(s.quantity) FROM `data` as s WHERE s.category = 'Category4' AND s.customer_id = e.customer_id) as quantity4, 
  (SELECT SUM(s.sales)    FROM `data` as s WHERE s.category = 'Category4' AND s.customer_id = e.customer_id) as sales4,
  (SELECT SUM(s.quantity) FROM `data` as s WHERE s.category = 'Category5' AND s.customer_id = e.customer_id) as quantity5, 
  (SELECT SUM(s.sales)    FROM `data` as s WHERE s.category = 'Category5' AND s.customer_id = e.customer_id) as sales5,
  (SELECT SUM(s.quantity) FROM `data` as s WHERE s.category = 'Category6' AND s.customer_id = e.customer_id) as quantity6, 
  (SELECT SUM(s.sales)    FROM `data` as s WHERE s.category = 'Category6' AND s.customer_id = e.customer_id) as sales6,
  (SELECT SUM(s.quantity) FROM `data` as s WHERE s.category = 'Category7' AND s.customer_id = e.customer_id) as quantity7, 
  (SELECT SUM(s.sales)    FROM `data` as s WHERE s.category = 'Category7' AND s.customer_id = e.customer_id) as sales7
FROM `data` as e
Group by e.customer_id
Order by quantity1

Is it possible to avoid this? :) Thank you

What is your expected result? Please describe the more details with data. — Majbah Habib, Mar 20 '16 at 12:15
While I understand your reasoning, I would still propose to use something like jQuery to make your final table sortable. That way you can avoid the huge SQL queries and even add some usability to the page. — Paul, Mar 20 '16 at 12:37

score 0 · Answer 1 · edited May 23 '17 at 12:31

As I understand, you are trying to pivot your table converting category row values into columns. Unfortunately, MySQL doesn't have such functions (like T-SQL). However, it's a common problem and it's well discussed here. So I would rewrite your query using CASE instead of subqueries like this:

SELECT 
  e.customer_id, 
  (SUM(CASE WHEN e.category = 'Category1' THEN e.quantity END)) as quantity1, 
  (SUM(CASE WHEN e.category = 'Category1' THEN e.sales END)) as sales1,
  (SUM(CASE WHEN e.category = 'Category2' THEN e.quantity END)) as quantity2, 
  (SUM(CASE WHEN e.category = 'Category2' THEN e.sales END)) as sales2,
  (SUM(CASE WHEN e.category = 'Category3' THEN e.quantity END)) as quantity3, 
  (SUM(CASE WHEN e.category = 'Category3' THEN e.sales END)) as sales3,
  (SUM(CASE WHEN e.category = 'Category4' THEN e.quantity END)) as quantity4, 
  (SUM(CASE WHEN e.category = 'Category4' THEN e.sales END)) as sales4
  # and so on 
FROM `data` as e
Group by e.customer_id
Order by quantity1

Advanced MySQL Query (Group By and Sort)

1 Answers1