Find percentage for each item on each date-CodePudding

Given Table:

Date            Item    Sales
-----------------------
2021-01-01       A      2000
2021-01-01       B      3000
2021-01-01       C      5000
2021-01-02       A      5000
2021-01-02       B      8000
2021-01-02       C      7000

The question is to find the respective percentage of sales for each item to the daily total. Like percentage of sales of item 'A' to total sales of '2021-01-01'.

I need output as the below table:

Date          Item    Sales   Percent
-------------------------------------
2021-01-01     A      2000     20
2021-01-01     B      3000     30
2021-01-01     C      5000     50
2021-01-02     A      5000     25
2021-01-02     B      8000     40
2021-01-02     C      7000     35

Please help me in this question. I have issue in query formation. Please provide suggestions for MS - SQL.

CodePudding user response：

This query uses a sub-query with group by to get the daily totals for use in calculating the Percent column.

create table sales (
date_sold Date,
Item varchar(25),
Sales int);

insert into sales values
('2021-01-01','A',2000),
('2021-01-01','B',3000),
('2021-01-01','A',5000),
('2021-01-02','B',8000),
('2021-01-02','C',7000);

select * from sales;

date_sold  | Item | Sales
:--------- | :--- | ----:
2021-01-01 | A    |  2000
2021-01-01 | B    |  3000
2021-01-01 | A    |  5000
2021-01-02 | B    |  8000
2021-01-02 | C    |  7000

select
  s.date_sold,
  item,
  sales,
  (100*sales)/total "Percent"
from
sales s
join
(select 
  date_sold,
  sum(sales) total
  from sales
  group by date_sold) ss
on s.date_sold = ss.date_sold
order by 
  date_sold,
  item;

date_sold  | item | sales | Percent
:--------- | :--- | ----: | ------:
2021-01-01 | A    |  2000 |      20
2021-01-01 | A    |  5000 |      50
2021-01-01 | B    |  3000 |      30
2021-01-02 | B    |  8000 |      53
2021-01-02 | C    |  7000 |      46

db<>fiddle here

CodePudding user response：

join table with itself in subquery as follows and use cast for numerator as follows

SELECT a.date,
       item,
       sales,
       Cast(sales AS FLOAT) * 100 / salessum AS percentage
FROM   sales a
       JOIN (SELECT Sum(sales) salessum,
                    date
             FROM   sales a
             GROUP  BY date) b
         ON a.date = b.date

or use window function form of sum as follows

SELECT
    Date,Item,Sales,
    100 * Sales / SUM(Sales) OVER (partition by date ) AS Percentage
FROM Sales  a

CodePudding user response：

You can use the sum window function:

select 
  *, 
  100.0 * sum(Sales) over(partition by Date, Item) / sum(Sales) over(partition by date) as "Percent" 
from 
  table_name;

Fiddle