关于按查询分组的问题 [英] question about group by query
本文介绍了关于按查询分组的问题的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
你好我正在做一个项目,我必须找到客户订单总数
i写下查询后
< br $>
Hello I am doing one project in that i have to find the total number of customer order
i write the query following
SELECT tbl_customer.*, tbl_order.*, tbl_order_status.*, tbl_item.* ,count(tbl_order.order_id)
FROM tbl_order INNER JOIN tbl_item ON tbl_order.item_id = tbl_item.item_id INNER JOIN tbl_order_status ON tbl_order.order_id = tbl_order_status.order_id INNER JOIN tbl_customer ON tbl_order.customer_id = tbl_customer.customerid
group by tbl_customer.customerid
但是当我运行该查询时我得到了以下的恐怖
它不包含聚合函数或逐个子句
Plz尽可能快地为我提供solustionas因为我必须提交项目
为我提供了可能的例子
but when I run that query I got following eror
It does not contain aggregate function or group by clause
Plz provide me solustionas fast as possible because I have to submit the project
provide me possible if example
推荐答案
另一种方法是,使用CTE分组一个唯一的标识符和被分组的数据,然后加入。
The other way around it is, use a CTE to group a unique identifier and the data being grouped, then join it in.
with grouping as
(
select customer_id, count(order_id) as orderCount from tbl_order group by customer_id
)
SELECT tbl_customer.*, tbl_order.*, tbl_order_status.*, tbl_item.* , grouping.orderCount
FROM tbl_order INNER JOIN
tbl_item ON tbl_order.item_id = tbl_item.item_id INNER JOIN
tbl_order_status ON tbl_order.order_id = tbl_order_status.order_id INNER JOIN
tbl_customer ON tbl_order.customer_id = tbl_customer.customerid
inner join grouping on tbl_order.order_id = grouping.order_id
group by tbl_customer.customerid
我的签名块链接到我关于SQL的文章,第三个是关于如何CTE工作。
My signature block links to my articles on SQL, the third is on how CTEs work.
Usig分组需要遵循一个简单的规则:什么不是聚合的,必须在group by子句中。因此,如果在投影部分中使用了这么多 table。*
,则需要在group by子句中包含所有软管字段。这没什么意义。所以我建议限制显示的字段数,包括那些不在聚合中的字段(每个不在计数中)。
Usig grouping requires following a simple rule: what's not in aggregate, has to be in group by clause. So if you use so many table.*
in the projection part you need to include all hose fields in the group by clause. Which makes no much sense. So I suggest to restrict the number of fields displayed, and include those that are not in an aggregation (every one not in the count).
这篇关于关于按查询分组的问题的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文