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
(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?
Multiply the value of "X" and "Y" by 100 to obtain the percent share of the total for each group. Completing the example: 0.56 times 100 equals 56 percent, which is the percentage of the people in the movie theater who are men. Similarly, 0.44 times 100 equals a share of 44 percent of people who are women at the movie.
There is no built-in operator that calculates percentages in SQL Server. You have to rely on basic arithmetic operations i.e. (number1/number2 x 100) to find percentages in SQL Server.
Percentage can be calculated by dividing the value by the total value, and then multiplying the result by 100. The formula used to calculate percentage is: (value/total value)×100%.
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]
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]
The most upvoted answer doesn't seem correct.
The OP has the expected answer
for example on 1-1-13 Joe sold 3 units out of 4 total for the day (75%)
and yet the upvoted answer shows 60%.
Instead of summing over all data it should be partitioned by day, here's a better example:
select [name],[salesdate], COUNT(*) as dayTotal,
SUM(COUNT(*)) over(PARTITION BY salesdate) as AllDaySales,
(COUNT(*) * 1.0) / SUM(COUNT(*)) over(PARTITION BY salesdate) as dayPercent
FROM [dbo].[sales]
group by [name], [salesdate]
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With