Search code examples
sqlsql-servermssql-jdbc

Average quantity per month on a table for a specific row SQL

I have the below table:

        MONTHNUMBER | MonthName | ProductName | QTY | AMOUNT | AVG qty/mos|
      |-------------|-----------|-------------|-----|--------|------------|
      | 1           | January   | Thingamabob | 11  | 100.00 |            |
      | 1           | January   | Widgets     | 18  | 150.00 |            |
      | 2           | February  | Thingamabob | 10  | 100.00 |            |
      | 2           | February  | Widgets     | 6   | 150.00 |            |
      | 3           | March     | Thingamabob | 8   | 100.00 |            |
      | 3           | March     | Widgets     | 20  | 150.00 |            |
      | 4           | April     | Thingamabob | 13  | 100.00 |            |
      | 4           | April     | Widgets     | 21  | 150.00 |            |
      | 5           | May       | Thingamabob | 10  | 100.00 |            |
      | 5           | May       | Widgets     | 17  | 150.00 |            |

Is it possible to get the average QTY for every row's MonthName and add that to the AVG qty/mos column?

What I wanted for the table is to be like this:

        MONTHNUMBER | MonthName | ProductName | QTY | AMOUNT | AVG qty/mos|
      |-------------|-----------|-------------|-----|--------|------------|
      | 1           | January   | Thingamabob | 11  | 100.00 |    14.5    |
      | 1           | January   | Widgets     | 18  | 150.00 |    14.5    |
      | 2           | February  | Thingamabob | 10  | 100.00 |      8     |
      | 2           | February  | Widgets     | 6   | 150.00 |      8     |
      | 3           | March     | Thingamabob | 8   | 100.00 |     14     |
      | 3           | March     | Widgets     | 20  | 150.00 |     14     |
      | 4           | April     | Thingamabob | 13  | 100.00 |    16.5    |
      | 4           | April     | Widgets     | 21  | 150.00 |    16.5    |
      | 5           | May       | Thingamabob | 10  | 100.00 |    13.5    |
      | 5           | May       | Widgets     | 17  | 150.00 |    13.5    |

I've tried the below script and I would get the same result as the QTY. 

SUM(QTY)/COUNT(DISTINCT(MonthName))

I hope this is possible with SQL.

Solution

  • you could use monthname in partition as well

        select a.*,avg(QTY) over(partition by MonthName) as avgqty
    from tab a

    But i think it is better to use month and year both should be in partition by because january month of which year it may also need of yours in future