CODEWITHSUNDEEP
sqlsql-server
Robert
Robert

Reputation: 15

How do return a percentage between 2 columns which is grouped by another column in SQL?

I have a table with 3 columns. DateRangeFields, IsOutOfTarget and AmountOfJobs.

What I need is to group results by DateRangeFields and show the percentage of jobs that are out of target for that date range. Data shown below
I tried to create a table showing the data but Stackoverflow doesnt seem to allow that, so I created it in Dbfiddle.

https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=ae9ce68d96f0d88a3f78ad6987010f56

Upvotes: 1

Views: 66

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Just use conditional aggregation:

Select DateRangeFields,
       (SUM(CASE WHEN IsOutOfTarget = 'True' THEN AmountOfJobs END) * 1.0 /
        SUM(AmountOfJobs)
       ) as ratio
From @MyTable t
Group By DateRangeFields;

Upvotes: 1

Singhi John
Singhi John

Reputation: 347

How about:

select sum(c1) as s_c1, sum(c2) as s_c2, divide(s_c1, s_c2) as pct from tab group by c3;

Here divide is a function you can find in the official documents. I am not sure.

And it's very long for me not touching SQL. So you konw, the statement I provided above maybe not runnable.

Upvotes: 0

Ladi Oyeleye
Ladi Oyeleye

Reputation: 154

Maybe not the cleanest way to do this but:

Select A.DateRangeFields, CAST(OutOfTargetJobs AS FLOAT)/CAST(OutOfTargetJobs+InTargetJobs AS FLOAT) As Percentage FROM (
Select DateRangeFields, SUM(AmountOfJobs) AS OutOfTargetJobs 
From @MyTable
Where IsOutOfTarget = 'True'
Group By DateRangeFields, IsOutOfTarget
) A
Inner Join
(
Select DateRangeFields, SUM(AmountOfJobs) AS InTargetJobs From @MyTable
Where IsOutOfTarget = 'False'
Group By DateRangeFields, IsOutOfTarget
) B on A.DateRangeFields = B.DateRangeFields;

Upvotes: 0

Related Questions