Advanced MySQL Query (Group By and Sort)
<p>Please take a look at <a href="http://sqlfiddle.com/#!9/3a4d84/1/0" rel="nofollow">http://sqlfiddle.com/#!9/3a4d84/1/0</a></p>

<p>I have this table</p>

<pre><code>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);
</code></pre>

<p>and when I use this query </p>

<pre><code>SELECT 
  customer_id, 
  category, 
  sum(quantity) as quantity, 
  sum(sales) as sales 
FROM `data` 
GROUP BY customer_id, category
</code></pre>

<p>I get results like this</p>

<pre><code>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
...
</code></pre>

<p>then with PHP, I build a table like this</p>

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

<p>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".</p>

<p>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...</p>

<p>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?</p>

<p>Thank you very mutch. Best Regards ben   </p>

<hr>

<p>Update, okey now I have this 'horror' query</p>

<pre><code>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
</code></pre>

<p>Is it possible to avoid this? :) Thank you</p>