CODEWITHSUNDEEP
sqlsql-servert-sqlrowscalculated-columns
VS1SQL
VS1SQL

Reputation: 155

Divide row by sum of column to get a percentage

I have the below table:

Pets | Monday| Tuesday| Wednesday| Thursday| Friday
Dogs | 12    |  11    |    8     |     3   |   0
Cats | 16    |  13    |    5     |     8   |   4
Fish | 45    |  27    |    20    |     35  |   22
Birds| 3     |   3    |    3     |     3   |   2

i am trying to return the percentage of each pet compared to the total of pets by the latest day so the return example would something like this:

Pets | Friday| Percentage
Dogs |  0    |     0%
Cats |  4    |     14.2%
Fish |  22   |     78%
Birds|  2    |     8.3%

I want to do this daily where I have set up a script that automatically fills in the next day (Saturday) and so the new table will need to show saturdays results.

I was thinking along the lines of the script below, I need to throw in some cast statements etc to get it to return a table but wanted to see if I was on the right lines:

SELECT Friday / (SELECT SUM([Friday]) FROM PetSitting) from PetSitting group by pets

The difficulty here is getting SQL to recognise one row-column then divide it by the same column to get a percentage, then move on to the next row and do the same etc until you get a list of percentages like the desired outcome above.

Upvotes: 1

Views: 4298

Answers (3)

Jonathan Willcock
Jonathan Willcock

Reputation: 5245

I would encourage you to have a look at your table design. I give here an example, using a table variable instead of a table (so that you can run it in a query window):

declare @pets table
(
Pet varchar(10),
SaleNumber int,
SaleDate date
)
INSERT INTO @pets VALUES
('Dogs', 12, '2017-08-07'),
('Dogs', 11, '2017-08-08'),
('Dogs', 8, '2017-08-09'),
('Dogs', 3, '2017-08-10'),
('Dogs', 0, '2017-08-11'),
('Cats', 16, '2017-08-07'),
('Cats', 13, '2017-08-08'),
('Cats', 5, '2017-08-09'),
('Cats', 8, '2017-08-10'),
('Cats', 4, '2017-08-11'),
('Fish', 45, '2017-08-07'),
('Fish', 27, '2017-08-08'),
('Fish', 20, '2017-08-09'),
('Fish', 35, '2017-08-10'),
('Fish', 22, '2017-08-11'),
('Birds', 3, '2017-08-07'),
('Birds', 3, '2017-08-08'),
('Birds', 3, '2017-08-09'),
('Birds', 3, '2017-08-10'),
('Birds', 2, '2017-08-11')

declare @lastdate date = (SELECT max(saledate) as lastdate FROM @pets)
declare @lastsum float = (SELECT sum(salenumber) as petsum FROM @pets 
WHERE saledate = @lastdate)

SELECT Pet, SaleNumber, Datename(dw,saledate) AS [Day of Week], 
salenumber / @lastsum as [Percentage of Day's Sales]
FROM @pets WHERE saledate = @lastdate

The result looks like this:

Pet SaleNumber  Day of Week Percentage of Day's Sales
Dogs    0   Friday  0
Cats    4   Friday  0.142857142857143
Fish    22  Friday  0.785714285714286
Birds   2   Friday  0.0714285714285714

Laying out your table like this, means that you can simply add records every day, without having to change your table, and the above query will automatically pick up the last days values.

Upvotes: 1

John Cappelletti
John Cappelletti

Reputation: 81970

If I understand your question, perhaps a little dynamic SQL

Example

Declare @Day varchar(50)='Friday'

Declare @SQL varchar(max) = '
Select Pets
      ,'+@Day+'     = sum('+@Day+') over (Partition By Pets)
      ,Percentage = format(sum('+@Day+'*1.0) over (Partition By Pets) / sum('+@Day+') over (),''0.#%'')
 From  YourTable
'
Exec(@SQL)

Returns

enter image description here

Upvotes: 0

S3S
S3S

Reputation: 25112

Sounds like a simple aggregate. Also, Adding more columns won't change the results. I just limited the sample data for clarity.

declare @table table (Pets varchar(8), Friday int)
insert into @table
values
('Dogs',0),
('Cats',4),
('Fish',22),
('Birds',2)


select
    Pets
    ,Friday
    ,Percentage = convert(decimal(3,1),100 * (Friday / ((Sum(Friday) over (order by (select null))) * 1.0)))
from 
    @table
group by
    Pets
    ,Friday

RETURNS

+-------+--------+------------+
| Pets  | Friday | Percentage |
+-------+--------+------------+
| Birds |      2 |        7.1 |
| Cats  |      4 |       14.3 |
| Dogs  |      0 |        0.0 |
| Fish  |     22 |       78.6 |
+-------+--------+------------+

Upvotes: 2

Related Questions