SQL percentage of total items by group

Question

I have the following pseudo-table which shows product orders:

+---------+------+--------------+---------------+
| OrderID | Year | PriorityCode | ShippedOnTime |
+---------+------+--------------+---------------+
|       1 | 2014 | A            | Y             |
|       2 | 2014 | B            | Y             |
|       3 | 2014 | A            | N             |
|       4 | 2015 | C            | Y             |
|       5 | 2015 | B            | Y             |
|       6 | 2015 | A            | N             |
|       7 | 2015 | A            | N             |
|       8 | 2015 | B            | N             |
|       9 | 2015 | C            | Y             |
|      10 | 2015 | C            | Y             |
+---------+------+--------------+---------------+

I need to find a way to query to find percentages of ShippedOntime grouped by PriorityCode, not as a total number of rows. For Example:

PriorityCode: A - Total 4, 1 was shipped on time = 25%

PriorityCode: B - Total 3, 2 were shipped on time = 33.3%

PriorityCode: C - Total 3, 3 were shipped on time = 100%

+--------------+------------+
| PriorityCode | Percentage |
+--------------+------------+
| A            | 25         |
| B            | 33.3       |
| C            | 100        |
+--------------+------------+

Been looking into using the Over() function and then Grouping the results, but cant seem to figure it out.

Id like to also be able to group it by year also, but small steps!

Answer 1

You can use conditional statements inside a group by function, such as count or sum to achieve the desired output:

select PriorityCode, sum(case when ShippedOnTime="Y" then 1 else 0 end)/count(*) * 100 as percentage
from table
group by PriorityCode

Answer 2

This should do the trick:

SELECT 
  Year,
  PriorityCode, 
  CAST(SUM(CASE WHEN ShippedOnTime = 'Y' 
                THEN 100.0 ELSE 0
                END)
   / COUNT(*) as DECIMAL(4,1)) Percentage
FROM yourtable
GROUP BY
  PriorityCode, Year