How to create a new SQL table with Mean, Median, and Mode?

Question

Ok, so I am a new to SQL that's why I am asking this question.

I have got a table called: kpi_notification_metrics_per_month This table has 2 columns:

• Date
• NotificationCount

I want to create a brand new table that will show

• Mean
• Median
• Mode

For the NotificationCount column.

Example table:

Date    NotificationCount
01/04/2018 00:00    0
31/03/2018 00:00    0
25/03/2018 00:00    0
24/03/2018 00:00    0
22/03/2018 00:00    0
18/03/2018 00:00    0
17/03/2018 00:00    0
14/03/2018 00:00    0
11/03/2018 00:00    0
07/04/2018 00:00    1
26/03/2018 00:00    1
21/03/2018 00:00    1
15/03/2018 00:00    1
13/03/2018 00:00    1
12/03/2018 00:00    1
10/03/2018 00:00    1
08/04/2018 00:00    2
30/03/2018 00:00    2
09/03/2018 00:00    2
08/03/2018 00:00    2
20/03/2018 00:00    3
19/03/2018 00:00    4
02/04/2018 00:00    9
23/03/2018 00:00    11
27/03/2018 00:00    22
03/04/2018 00:00    28
28/03/2018 00:00    34
04/04/2018 00:00    39
05/04/2018 00:00    43
29/03/2018 00:00    47
06/04/2018 00:00    50
16/03/2018 00:00    140

Expected results:

Mean    Median  Mode
13.90625    1   0

Answer 1

Mean: Use Avg()

Select Avg(NotificationCount)
From   kpi_notification_metrics_per_month

Median: Order by ASC and DESC for TOP 50 Percent of data, find the middle one.

Select ((
        Select Top 1 NotificationCount
        From   (
                Select  Top 50 Percent NotificationCount
                From    kpi_notification_metrics_per_month
                Where   NotificationCount Is NOT NULL
                Order By NotificationCount
                ) As A
        Order By NotificationCountDESC) + 
        (
        Select Top 1 NotificationCount
        From   (
                Select  Top 50 Percent NotificationCount
                From    kpi_notification_metrics_per_month
                Where   NotificationCount Is NOT NULL
                Order By NotificationCount DESC
                ) As A
        Order By NotificationCount Asc)) / 2

Mode: Get counts of each value set and get the top 1 row in DESC order.

SELECT TOP 1 with ties NotificationCount
FROM   kpi_notification_metrics_per_month
WHERE  NotificationCount IS Not NULL
GROUP  BY NotificationCount
ORDER  BY COUNT(*) DESC

All worked in Sql Server 2014.

Reference: http://blogs.lessthandot.com/index.php/datamgmt/datadesign/calculating-mean-median-and-mode-with-sq/