Calculate percentage between two columns of difference table in SQL Server

smile

SOLVED CLOSED ACTIVE ANSWERED
User: smile
Posted: on Sep 26, 2021 11:13 PM
Forum: SQL Server
Answer: 1
Views: 5172

i have two tables like that

tblA

SireName	Total
Milo	1
Shootle	1
Twist	3

tblB

SireName	Total
Milo	1
Shootle	2
Twist	5

Required output

SireName	Total
Milo	100
Shootle	50
Twist	60

Formula: tblA value / tblb Value example

1/1 * 100 = 100

1/2* 100 = 50

3/5 * 100 = 60

select SireName as 'Sire',count(SireName) as 'Total',S_Type as 'Sire',count(S_Type) as 'Total' 
from tblPregTest as p
right join tblBreading as b on p.SireName=b.S_Type 
where P_Result='Positive' group by SireName, S_Type

Download FREE API for Word, Excel and PDF in ASP.Net

dharmend...

0 5

Reply
ANSWER
User: dharmendr
Replied: on Sep 27, 2021 06:57 AM
Report

Hi smile,

Refer below query.

SQL

CREATE TABLE #tblA(SireName VARCHAR(10),Total INT)
INSERT INTO #tblA VALUES('Milo',1)
INSERT INTO #tblA VALUES('Shootle',1)
INSERT INTO #tblA VALUES('Twist',3)
	
CREATE TABLE #tblB(SireName	VARCHAR(10),Total INT)
INSERT INTO #tblB VALUES('Milo',1)
INSERT INTO #tblB VALUES('Shootle',2)
INSERT INTO #tblB VALUES('Twist',5)
	

SELECT p.SireName AS 'Sire',
	(CAST(p.Total AS FLOAT)/(SELECT Total FROM #tblB b WHERE b.SireName = p.SireName))*100 AS 'Total'
FROM #tblA p

DROP TABLE #tblA
DROP TABLE #tblB

Output

Sire	Total
Milo	100
Shootle	50
Twist	60

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html

Calculate percentage between two columns of difference table in SQL Server

Company

Explore

Follow us

Contact Us