总结MySQL查询的输出

Question

我有以下疑问：

SELECT extended_number, IF(COUNT(extension_id)<5,1,0) AS 'fax5', 
  IF(COUNT(extension_id)>5 AND COUNT(extension_id)<11,1,0) AS 'fax10',
  IF(COUNT(extension_id)>10,1,0) AS 'fax10+', COUNT(extension_id) AS 'fax_total'
FROM fax_authorized fa, extension e
WHERE fa.extension_id = e.id
GROUP BY extended_number; 

输出如下所示：

+-----------------+------+-------+--------+-----------+
| extended_number | fax5 | fax10 | fax10+ | fax_total |
+-----------------+------+-------+--------+-----------+
| 0009*004        |    1 |     0 |      0 |         1 |
| 0139*601        |    0 |     1 |      0 |         6 |
| 0139*743        |    1 |     0 |      0 |         2 |
| 0139*996        |    1 |     0 |      0 |         1 |
+-----------------+------+-------+--------+-----------+

我想要根据左边(extended_number，4)来总结这个数据，这样上面的示例0139就会有fax5=2，fax10=1，fax_total=9。

我已经试过了，它不起作用：

SELECT extended_number, SUM(IF(COUNT(extension_id)<5,1,0)) AS 'fax5',   
  SUM(IF(COUNT(extension_id)>5 AND COUNT(extension_id)<11,1,0)) AS 'fax10', 
  SUM(IF(COUNT(extension_id)>10,1,0)) AS 'fax10+', 
  COUNT(LEFT(extension_id,4)) AS 'fax_total'
FROM fax_authorized fa, extension e
WHERE fa.extension_id = e.id
GROUP BY LEFT(extended_number,4); 

编辑:我使用了下面Saharsh Shah的答案来解决这个问题，但我提出了另一个我认为值得添加的问题。我试图从他的嵌套子查询解决方案中创建一个视图，但无法实现，因为视图中不允许使用子查询。所以我创建了两个相关的视图，如下所示：

CREATE VIEW vw_xoom_faxdetail AS 
  SELECT LEFT(extended_number,4) client_id, 
   IF(COUNT(extension_id)<5,1,0) AS 'fax5', 
   IF(COUNT(extension_id)>5 AND COUNT(extension_id)<11,1,0) AS 'fax10',
   IF(COUNT(extension_id)>10,1,0) AS 'fax10plus', 
   COUNT(extension_id) AS 'fax_total'
 FROM fax_authorized fa, extension e WHERE fa.extension_id = e.id
 GROUP BY client_id;

CREATE VIEW vw_xoom_fax AS 
 SELECT client_id, SUM(fax5) fax5, SUM(fax10) fax10,
  SUM(fax10plus) 'fax10+', SUM(fax_total) fax_total 
 FROM vw_xoom_faxdetail
 GROUP BY client_id;

Answer 1

试试这个：

SELECT extended_number, SUM(fax5) fax5, SUM(fax10) fax10, SUM(fax10plus) 'fax10+', SUM(fax_total) fax_total 
FROM (SELECT LEFT(extended_number,4) extended_number, IF(COUNT(extension_id)<5,1,0) AS 'fax5', 
             IF(COUNT(extension_id)>5 AND COUNT(extension_id)<11,1,0) AS 'fax10',
       IF(COUNT(extension_id)>10,1,0) AS 'fax10plus', COUNT(extension_id) AS 'fax_total'
      FROM fax_authorized fa, extension e WHERE fa.extension_id = e.id
      GROUP BY extended_number) AS A 
GROUP BY extended_number;