问题描述
我有一张桌子，每个销售员每天有 1 条记录
I have a table with 1 record per sale per salesperson per day
NAME DATE
joe 1-1-13
joe 1-1-13
joe 1-1-13
dave 1-1-13
joe 1-2-13
我用它来创建 &填充表格
I used this to create & populate the table
create table #sales (name varchar(10), salesdate date )
insert into #sales (name, salesdate)
values ('joe', '01-01-2013'), ('joe','01-01-2013'),
('joe', '01-01-2013'), ('dave','01-01-2013'),
('joe','01-02-2013')
我想要一个查询来拉出每个销售人员每天销售额的百分比
I want a query to pull up the percent of each salesperson's sales by day
(例如，在 1-1-13 日，乔当天售出了 4 件中的 3 件(75%)但我不知道 SQL 如何拉出当天所有销售额的总和，而不管销售人员如何
(for example on 1-1-13 Joe sold 3 units out of 4 total for the day (75%)but I dont know how the SQL can pull up the daily total of all sales for the day regardless of salesperson
这和我得到的一样接近.
This is as close as I got.
select name, salesdate, count(*) as "dailyTotal"
from #sales
group by name, salesdate
如何包含每日总计，以便它可以用于计算当天的总计百分比?
How can I include the daily total that is so that it can be used in calculating percent total for the day?
推荐答案
这不是最优雅的方法，但你可以试试这个 -
Not the most elegant way to do it, but you can try this -
select [name],[salesdate], COUNT(*) as dayTotal,
SUM(COUNT(*)) over() as AllSales,
(COUNT(*) * 1.0) / SUM(COUNT(*)) over() as dayPercent
FROM [dbo].[sales]
group by [name], [salesdate]
我删除了你表名中的#.顺便说一句，这段代码取决于 OVER() 子句.您可以了解如何自行截断多余的零.
I removed the # in your table name. Btw, this code depends on OVER() clause. You can find out how to truncate the excess zeros yourself.
name salesdate dayTotal AllSales dayPercent
dave 2013-01-01 1 5 0.200000000000
joe 2013-01-01 3 5 0.600000000000
joe 2013-01-02 1 5 0.200000000000
HTH.
如果您觉得该查询太复杂，请先查看此查询.它会让你对我正在尝试做的事情有一个想法.
If that query looks too complicated to you, then look at this one first. It will give you an idea of what I am trying to do.
select [name],[salesdate], COUNT(*) as dayTotal,
SUM(COUNT(*)) over() as AllSales
FROM [dbo].[sales]
group by [name], [salesdate]
这篇关于如何计算group by语句中总数的百分比?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！