列中的COUNT值和生成结果表mysql

问题描述：

To preface this question - I know I'm missing something really obvious here but it is Friday afternoon!

I'm trying to COUNT to number of times certain values appear within the same column and produce a table of the results.

Here's my code so far:

MYSQL

SELECT COUNT(order_status) as printed
FROM orders WHERE order_status = 'Printed Order'

UNION

SELECT COUNT(order_status) as charged
FROM orders WHERE order_status = 'Charged Order'

UNION

SELECT COUNT(order_status) as exchanged
FROM orders WHERE order_status = 'Exchanged Order'

UNION

SELECT COUNT(order_status) as refunded
FROM orders WHERE order_status = 'Refunded Order'

UNION

SELECT COUNT(order_status) as cancelled
FROM orders WHERE order_status = 'Cancelled Order'
GROUP BY order_status

Result of the above query

printed
-------
224
19190
593
2618
2899

The code is producing the correct figures, however, I would prefer the result to look as follows:

Desired result

printed - 224
charged - 19190
exchanged - 593
refunded - 2618
cancelled -2899

This way I can easily reference them via associative array call i.e. $order_status['printed']

Any help would be great.

前言这个问题 - 我知道我错过了一些非常明显的东西，但现在是星期五下午！ p >

我正在尝试计算某些值出现在同一列中的次数，并生成结果表。 p>

到目前为止，这是我的代码： p>

MYSQL strong> p>

   SELECT COUNT（order_status）打印
FROM订单WHERE order_status ='打印订单'
 
UNION 
 
SELECT COUNT（order_status）收费
FROM订单WHERE order_status ='收费订单'
 
UNION 
 
SELECT COUNT  （order_status）交换
FROM命令WHERE order_status ='交换订单'
 
UNION 
 
SELECT COUNT（order_status）作为退款
FROM订单WHERE order_status ='退款订单'
 
UNION 
 
SELECT COUNT（order_status）  ）取消
FROM命令WHERE order_status ='取消订单'
GROUP BY order_status 
  code>  pre> 
 
  上述查询的结果 strong>  p>  
 
 
 打印
 ------- 
224 
19190 
593 
2618 
2899 
  code>  pre> 
 
 代码是 但是，如果产生正确的数字，我希望结果如下： p> 
 
 
 期望的结果 strong>  p> 
 
 
 打印 -  224 
charged  -  19190 
exchanged  -  593 
refunded  -  2618 
cancelled -2899 
  c  ode>  pre> 
 
 这样我可以通过关联数组调用轻松引用它们，即 $ order_status ['printed']  code>  p> 
 
 
 任何帮助都会很棒。   p> 
  div>

答

Add a column specifying the type. The easiest way is to use group by:

select order_status, count(*)
from orders o
group by order_status;

答

try:

SELECT order_status, COUNT(order_status) as printed
FROM orders WHERE order_status = 'Printed Order'
GROUP BY order_status

答

If you use PDO, set the fetch mode to create associative arrays by default by:

$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);

列中的COUNT值和生成结果表mysql

相关推荐