CODEWITHSUNDEEP
sqlsql-serverpercentage
Sung
Sung

Reputation: 209

How do I calculate the percentage of one column to another?

I have a query:

SELECT CAST(SUBMITDATE as date) as 'Date'

,COUNT(*) as [Tickets Submitted]
,SUM(CASE WHEN STATUS = 'Closed' THEN 1 ELSE 0 END) as [Tickets Closed]

FROM Table1

    WHERE SUBMITDATE >= '2015-05-29'
      AND SUBMITDATE < '2015-06-03'


GROUP BY CAST(SUBMITDATE as date)

which produces the following result:

Date        Tickets Submitted   Tickets Closed
2015-05-29  1062                1040
2015-05-30  429                 416
2015-05-31  260                 250
2015-06-01  705                 654
2015-06-02  556                 515
2015-06-03  508                 452

I would like to calculate the percentage of tickets Closed. [Tickets Closed] divided by [Tickets Submitted].

When I try adding: ,SUM((CASE WHEN STATUS = 'Closed' THEN 1 ELSE 0 END)/COUNT(*)) as [Percentage of Tickets Closed]

I get the following error:

"Cannot perform an aggregate function on an expression containing an aggregate or a subquery."

Can anyone help?

Thanks so much.

Upvotes: 0

Views: 1300

Answers (2)

Sung
Sung

Reputation: 209

,CAST(CAST(SUM(CASE WHEN STATUS = 'Closed' THEN 1 ELSE 0 END) as FLOAT)/CAST(COUNT(*) as FLOAT)*100 as DECIMAL(5,2)) as [% of Tickets Closed]

is what I was looking for.

Here are the results I get:

Date        Tickets Submitted   Tickets Closed  % of Tickets Closed
2015-05-29  1062                1047            98.59
2015-05-30  428                 418             97.66
2015-05-31  260                 253             97.31
2015-06-01  703                 669             95.16
2015-06-02  555                 517             93.15
2015-06-03  507                 463             91.32

Upvotes: 0

Tab Alleman
Tab Alleman

Reputation: 31795

Don't put the COUNT inside the SUM. Try like this:

,CAST(SUM(CASE WHEN mrSTATUS = 'Closed' THEN 1 ELSE 0 END) AS float)/CAST(COUNT(*) AS float) as [Percentage of Tickets Closed]

The SUM and COUNT functions will produce integers, which means the division will also result in an integer. CAST them as floats to get a fraction instead.

Upvotes: 1

Related Questions