关于按查询分组的问题 [英] 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屋！