按日期组按产品id的SQL计数记录

Question

我使用Codeigniter，当我COUNT总量FROM wo_tbl_sales_items WHERE pdate GROUP BY pid时，我会遇到问题。查询没有给出正确的结果，我在下面显示我的代码，

$this->db->select("
IFNULL(FORMAT(SUM(`si`.`item_total`),2), '0.00') as total_items_amount,
IFNULL(COUNT(`si`.`item_qty`), 0) as total_items_count
");
$this->db->where('si.pdate =', $date);
$this->db->from('wo_tbl_sales_items si');
$this->db->group_by('si.pid');
$this->db->order_by('total_items_count', 'DESC');
$result = $this->db->get();
if($result->num_rows() > 0)
{
foreach($result->result() as $row){
$data['parts'][] = array(
'total_items_amount' => $row->total_items_amount,
'total_items_count' => $row->total_items_count
); 
}
}

我还展示了我的数据库表结构，

----------------------------------------------------------------------------
| mainid |   pid  |   item_name   |  item_qty  | item_total  |    pdate    |
----------------------------------------------------------------------------
|   1    |   164  |   Iphone 6s   |     2      |   2000      |  2018-07-21 |
|   2    |   164  |   Iphone 6s   |     1      |   1000      |  2018-07-21 |
|   3    |   164  |   Iphone 6s   |     1      |   1000      |  2018-07-21 |
|   4    |   165  |   Samsung A6  |     1      |   600       |  2018-07-21 |
----------------------------------------------------------------------------

我看结果是这样的，请看下面的结构。

-------------------------------------------------
|  Items Name    |    Qty   |  Total Amount     |
-------------------------------------------------
|   Iphone 6s    |     4    |      4000         |
|   Samsung A6   |     1    |       600         |
-------------------------------------------------

Answer 1

根据您的预期输出，您需要sumitem_qty而不计算item_qt

$this->db->select("
IFNULL(FORMAT(SUM(`si`.`item_total`),2), '0.00') as total_items_amount,
IFNULL(SUM(`si`.`item_qty`), 0) as total_items_count
");

普通sql看起来像

select si.pid,
si.item_name,
ifnull(sum(`si`.`item_qty`), 0) as total_items_count,
ifnull(format(sum(`si`.`item_total`),2), '0.00') as total_items_amount
from wo_tbl_sales_items si
where si.pdate = '2018-07-21' 
group by si.pid,si.item_name
order by total_items_count DESC

演示

尝试将格式化部分(FORMAT(….((从查询移动到应用程序代码。