Calculate Percentage of each group in SQL Server

smile
 
on Sep 20, 2021 01:14 AM
484 Views

Issue in sum and getting percentage in sql

I wrote the following query using union

SELECT 'Inseminated:' as 'Head' ,count(t1.TagID) 'Count' FROM tblBreading t1 LEFT JOIN tblPRegTest t2 ON t1.TagID = t2.TagID and t1.I_Date = t2.I_Date WHERE t2.TagID IS NULL
union all
SELECT 'Repeated:' as 'Head' ,count(t1.TagID) 'count' FROM tblPregTest t1 LEFT JOIN tblBreading t2 ON t1.TagID = t2.TagID WHERE P_Result='Negative' and t1.C_Date > t2.I_Date 
union all
SELECT 'Fresher:' as 'Head', count(TagID) 'count' from tblCalving where DATEDIFF(day, C_Date, CONVERT(CHAR(50), GETDATE(), 23)) < 45
union all
SELECT 'Open:' as 'Head', count(t1.TagID) 'count' FROM tblCalving t1 LEFT JOIN tblBreading t2 ON t1.TagID = t2.TagID WHERE t1.C_Date > t2.I_Date and DATEDIFF(day, t1.C_Date, CONVERT(CHAR(50), GETDATE(), 23)) > 45
union all
SELECT 'Insemination Free:' as 'Head', count(t1.TagID) 'count' FROM tblAnimal t1 LEFT JOIN tblBreading t2 ON t1.TagID = t2.TagID WHERE t2.TagID IS NULL;

it is showing me output like that

Head

Count

Inseminated

10

Repeated

1

Fresher

2

Open

0

Insemination Free

28

whereas I want output like that

Head

Count

Percentage

Inseminated

10

24.39 %

Repeated

1

2.43

Fresher

2

4.87

Open

0

0

Insemination Free

28

68.29

Total Count

41

 

where percentage formula is following

Inseminated Count / Total Count * 100

10 / 41 * 100 = 24.39

1 / 41 * 100 =  2.43

2 / 41 * 100 =  4.87

0 / 41 * 100 = 0

28 / 41 * 100 = 68.29

Total count value may be increase or decrease any time as it is dynamic sum.

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Sep 20, 2021 04:56 AM
on Sep 20, 2021 05:40 AM

Refer below query.

SQL

SELECT Head,Count 
INTO #Temp
FROM 
(
	SELECT 'Inseminated' as 'Head' , 10 'Count'
	union all
	SELECT 'Repeated' as 'Head' , 1
	union all
	SELECT 'Fresher' as 'Head', 2
	union all
	SELECT 'Open' as 'Head', 1
	union all
	SELECT 'Insemination Free' as 'Head', 28
)t

SELECT *,ROUND(CAST((CAST(Count AS DECIMAL)/(SELECT SUM(Count) FROM #Temp))*100 AS Float),2) 'Percentage' 
FROM #Temp

DROP TABLE #Temp

Output

Head Count Percentage
Inseminated 10 23.81
Repeated 1 2.38
Fresher 2 4.76
Open 1 2.38
Insemination Free 28 66.67

Your query will be like below.

SELECT Head,Count 
INTO #Temp
FROM 
(
	SELECT 'Inseminated:' as 'Head' ,count(t1.TagID) 'Count' FROM tblBreading t1 LEFT JOIN tblPRegTest t2 ON t1.TagID = t2.TagID and t1.I_Date = t2.I_Date WHERE t2.TagID IS NULL
	union all
	SELECT 'Repeated:' as 'Head' ,count(t1.TagID) 'count' FROM tblPregTest t1 LEFT JOIN tblBreading t2 ON t1.TagID = t2.TagID WHERE P_Result='Negative' and t1.C_Date > t2.I_Date
	union all
	SELECT 'Fresher:' as 'Head', count(TagID) 'count' from tblCalving where DATEDIFF(day, C_Date, CONVERT(CHAR(50), GETDATE(), 23)) < 45
	union all
	SELECT 'Open:' as 'Head', count(t1.TagID) 'count' FROM tblCalving t1 LEFT JOIN tblBreading t2 ON t1.TagID = t2.TagID WHERE t1.C_Date > t2.I_Date and DATEDIFF(day, t1.C_Date, CONVERT(CHAR(50), GETDATE(), 23)) > 45
	union all
	SELECT 'Insemination Free:' as 'Head', count(t1.TagID) 'count' FROM tblAnimal t1 LEFT JOIN tblBreading t2 ON t1.TagID = t2.TagID WHERE t2.TagID IS NULL
)t

SELECT *,ROUND(CAST((CAST(Count AS DECIMAL)/(SELECT SUM(Count) FROM #Temp))*100 AS Float),2) 'Percentage' 
FROM #Temp

DROP TABLE #Temp