如何计算每个销售人员每天的销售额

Question

我想算出一个销售人员每天完成多少笔销售。

我尝试了如下所示的查询。它返回每个销售人员每天每个销售额的销售额。最后一部分是问题所在，因为我想对每个销售人员每天的销售额进行分组。但group by条款不允许这样做。这是合乎逻辑的，因为查询匹配每个销售id的销售人员名称。因此，saleid必须包含在group by子句中，因为如果不包含该子句，则查询无法知道要返回关于销售人员- saleid对的哪个条目。至少我是这么认为的。

这是查询。

select
(select users.firstname + ' ' + users.lastname from users where id =
(select value from entdatadetails left join entfields on entfield_id = entfields.id where fld_code = 'order_salesman' and entdata_id = entdatas.id)) as salesman,
count(entdatas.id) as no_of_orders,
cast(entdatas.created_at as date),
entdatas.id
from
entdatadetails
left join entdatas
on entdatas.id = entdata_id
left join entfields
on entfields.id = entfield_id
where fld_code = 'order_salesman'
and isActive = 1
and cast(entdatas.created_at as date) < cast(getdate() as date)
group by cast(entdatas.created_at as date), entdatas.id
order by cast(entdatas.created_at as date) asc

下面是它生成的表的一个示例:

生成的表(由于敏感数据而被裁剪)

销售人员的名字没有显示，但通过他们的结尾可以知道有多少销售。我真正需要的是数据。从组中删除Id，但查询的结构方式不允许。

Answer 1

使用group_concat将所有条目id以逗号分隔，并按用户id(即销售人员id (entdata .salesperson_id))分组。请参考修改后的查询

SELECT
(SELECT users.firstname + ' ' + users.lastname
FROM users
WHERE id =
(SELECT value
FROM entdatadetails
LEFT JOIN entfields ON entfield_id = entfields.id
WHERE fld_code = 'order_salesman'
AND entdata_id = entdatas.id)) AS salesman,
count(entdatas.id) AS no_of_orders,
cast(entdatas.created_at AS date),
entdatas.id
FROM entdatadetails
LEFT JOIN entdatas ON entdatas.id = entdata_id
LEFT JOIN entfields ON entfields.id = entfield_id
WHERE fld_code = 'order_salesman'
AND isActive = 1
AND cast(entdatas.created_at AS date) < cast(getdate() AS date)
GROUP BY cast(entdatas.created_at AS date),
entfield_id
ORDER BY cast(entdatas.created_at AS date) ASC