计算并显示多少特定列结果 [英] Count and display how many of specific column result

问题描述

我有下表

|id|city|job|
|12|New York|Truck driver|
|13|Chicago|Postman|
|14|New York|Postman|
|15|Las Vegas|Dealer|
|16|Las Vegas|Dealer|
|17|New York|Post Office|

我希望能够计算每个城市有多少工作，然后返回十个城市以DESC顺序完成大多数工作。类似于以下内容

I want to be able to count how many jobs there is in each city and then return the ten cities with most job in an DESC order. Like the following

• 纽约（3）


• 拉斯维加斯（2）


• 芝加哥（1）

我要如何做到这一点？

我的代码如下

 $this->db->select('city');
 $this->db->select_sum('city', 'total');
 $this->db->group_by('city'); 
 $this->db->order_by('total', 'desc'); 
 $query = $this->db->get('job', 10);

foreach ($query->result() as $row) {
    $data[] = array(
        'total' => $row->total,
        'city' => $row->city
    );
}

return $data;

我从此线程中学到了灵感：具有活动记录的COUNT / GROUP BY吗？

I took inspiration from this thread: COUNT / GROUP BY with active record?

但似乎没有来解决问题。

But doesn't seems to get it right.

推荐答案

在这里，您去：-

$query = $this->db->query('SELECT city, COUNT(city) AS jobcount FROM `jobs` GROUP BY city ORDER BY jobcount DESC');

if ($query->num_rows() > 0)
    foreach ($query->result() as $row) 
        $data[] = array(
            'total' => $row->jobcount,
            'city'  => $row->city
        );

return $data;

不要忘记将'jobs'与您的表格名称

Dont forget to swap 'jobs' with your table name

这篇关于计算并显示多少特定列结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！