mysql - 内部连接两个表，并具有汇总和排序依据和分组依据

最近几天我一直呆在里面，真的不知道该怎么办。我有一个包含三个表的数据库来获取数据。我无法使用内部联接和排序依据的汇总。我有以下查询，返回结果。

SELECT  bb_catg.catg_name,
        bi_item_name,
        sum(bi_item_qty) as TotalQty,
        sum(bi_item_total) as Grandtotal
FROM bb_bill_items
    inner join bb_items
    on  bb_bill_items.item_id = bb_items.item_id
    inner join bb_catg
    on bb_items.catg_id  = bb_catg.catg_id
    inner join bb_bill
    on bb_bill_items.bil_id  = bb_bill.bil_id
where dateandtime = '$date' and paymenttype = '$Status'
group by bb_bill_items.item_id
order by bb_catg.catg_name

结果

catg_name | bi_item_name | TotalQty | Grandtotal
------    | ------------ |--------- |-----------
PASTRIES  |Cupcake       |1         |270
PASTRIES  |Eclair        |1         |150
PASTRIES  |Mousse        |1         |350
           ----------
             total       |3         |770

任何帮助将不胜感激。

最佳答案

您可以更改以下查询为您解决方案：

SELECT  bb_catg.catg_name,bi_item_name,
        sum(bi_item_qty) as TotalQty,sum(bi_item_total) as Grandtotal
FROM bb_bill_items
inner join bb_items
    on  bb_bill_items.item_id = bb_items.item_id
inner join bb_catg
on bb_items.catg_id  = bb_catg.catg_id
inner join bb_bill
    on bb_bill_items.bil_id  = bb_bill.bil_id
where dateandtime = '$date' and paymenttype = '$Status'
group by bb_catg.catg_name,bb_items.bi_item_name
order by bb_catg.catg_name

您可以在“分组依据子句”中添加其他列的汇总功能。