SQL - percentage from total

Transact-SQL
1

Hey Guys,

I have a table caled "Sales":

image
image936×241 10 KB

I wrote a query that returns the total sales in the sports and music departments in the first 3 months of 2016:

SELECT COUNT (*) AS TOTAL_SALES, Department
FROM Sales
WHERE DATE >= '2016-01-01'
AND DATE < '2016-04-01'
AND DEPARTMENT IN ('MUSIC','SPORT')
GROUP BY Department

But now I want to calculate the their relative percentage (MUSIC&SPORT) out of the department's total sales.

I know that I need to calculate it this way, but It returns me an error:
CAST((CAST(COUNT(TOTAL_SALES_MUSIC_2016_Q1) AS FLOAT) / CAST(COUNT(TOTAL_SALES_MUSIC_2016) AS FLOAT) *100) AS VARCHAR) + '%' AS cnv_rate

Thanks,

Michal

2

hi hope this helps

create data script

create table #Sales (Sale_Id int , [Date] date , Branch_Id int , Department varchar(20) , Num_Of_Products int ,Total_Price int , Branch_City varchar(20) ,Employee_Id int)
insert into #Sales select 12 ,'2016-11-13',5,'CLOTHING' ,12 ,400 ,'Natania' ,88
insert into #Sales select 111 ,'2014-12-01',1,'SPORT' ,2 ,100 ,'Tel Aviv' ,22
insert into #Sales select 112 ,'2015-01-02',2,'TOYS' ,10 ,155 ,'Herzelia' ,1
insert into #Sales select 113 ,'2013-04-16',3,'MUSIC' ,1 ,255 ,'Haifa' ,999
insert into #Sales select 444 ,'2016-01-30',7,'MUSIC' ,2 ,600 ,'Yahud' ,344
insert into #Sales select 3333 ,'2016-05-01',6,'ELECTRONICS',3 ,25000 ,'Tel Aviv' ,22
insert into #Sales select 8888 ,'2016-01-30',8,'CLOTHING' ,10 ,1000 ,'Yahud' ,333
insert into #Sales select 22200,'2016-01-22',4,'HOME' ,5 ,3000 ,'Hod Hasharon' ,78
select * from #Sales

SELECT 
     CAST 
	 (
		 ( 
			sum(CASE WHEN Department IN ( 'MUSIC','SPORTS' ) THEN Total_Price ELSE 0 END ) * 1.0 
			/ 
			sum(Total_Price) * 1.0 
		 ) 
			* 100.0 AS VARCHAR
	 ) 
		+ '%'
	 AS cnv_rate  
FROM 
    #Sales

image
image763×360 9.8 KB

1 Like
3

Hey, it really helped!
Thanks a lot

4

@Michal_Dv wrote:

Hey, it really helped!
Thanks a lot

Do you understand why it helped?

5

Yep - devide music and sport sells from total sells by using CASE statement

6

What I'm more concerned about is do you understand why @harishgg1 had to multiply by 1.0?

7

I think he did so because 1.0 represents 100%?

8

No. It's because of what happens if you divide two integers... integer division that results in a return of the number zero.

1 Like