Categorywise % of sales and returns of categories in the same code and Output

Transact-SQL
1

sqlQ

I have reached just till:
Select B.subcat, Round(Sum(A.total_amt),0) as Sales from A Left Join B On
A.catcode = B.catcode AND A.subcatcode = B.subcatcode
Where total_amt > 0 Group By B.subcat

*** Or Where total_amt < 0 for returns.

Is there a way to:

  1. Get % of Sales / Returns
  2. Get those in same query

This my SQL beginning, not much comfy with subqueries.

2

Hi

I tried this

I created some dummy data
Hope it helps
:slight_smile:
:slight_smile:

drop create data 
use tempdb 
go 


drop table A 
go 
 
create table A 
(
Tran_num int ,
SubCatCode int,
CatCode int,
[Sales/Return] int 
)
go 

drop table B
go 

create table B 
(
SubCatCode int,
SubCat varchar(100), 
Cat  varchar(100), 
CatCode int
)
go 

insert into A select 1,1,1,100
insert into A select 2,1,1,200
insert into A select 3,2,2,50
insert into A select 4,2,3,109
go 

insert into B select 1,'A','AB',1
insert into B select 2,'B','DF',2
insert into B select 2,'C','CE',3
go
SQL .. 
SELECT a.subcatcode, 
       a.[sales/return], 
       abc.total + a.[sales/return]                              AS TotalSales, 
       abc.total - a.[sales/return]                              AS TotalReturns 
       , 
       a.[sales/return] * 1.0 / ( abc.total + a.[sales/return] ) AS 
       '% Sales', 
       a.[sales/return] * 1.0 / ( abc.total - a.[sales/return] ) AS '% Returns' 
FROM   (SELECT Sum(a.[sales/return]) AS total 
        FROM   a) abc, 
       a
Results

image
image.png1678×387 8.93 KB

1 Like
3

Thank you so much Harish. This will be a good starter for me. Just one heads up:
For Sales/Return column, if a value is +ve it will be considered a sale and if a value is -ve it will be a considered a return. So it can expect both positive and negative values and basis those, sales and return columns in the output would be populated.

4

hi dave

its possible
to do +ve(Sales) and -ve(Returns)

with case statement
case when column >=0 then total + column
case when column <0 then total - column

1 Like
5

Thanks Harish. With some more trials and inputs from you, I came up with this code and this works as desired: Cat, Subcat, Sales (Positive sales), Returns (negative sales), %Sales, %Return (All grouped by Cat and Subcat)

***Table A (transaction table) renamed as C

---Start

Select B.cat, B.subcat, C.Sales, C.Return,

ROUND(100*Sales/(select Sum(Sales/Return) From A Where Sales/Return > 0),2) As PercentSales,

Round(100*Return/(select Sum(Sales/Return) From A Where Sales/Return < 0),2) as PercentReturn

From

(Select

catcode,

subcatcode,

Round(sum(case when Sales/Return > 0 then Sales/Return end),2) as Sales,

Round(sum(case when Sales/Return < 0 then Sales/Return end),2) as Return

from A Group by catcode, subcatcode) As C LEFT JOIN

B ON

C.catcode = B.catcode AND

C.subcatcode = B.subcatcode

Order by C.catcode, C.subcatcode

---End