CODEWITHSUNDEEP
sqlsql-servermssql-jdbc
Giancarlo Zaballero
Giancarlo Zaballero

Reputation: 37

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.

Upvotes: 1

Views: 63

Answers (2)

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31991

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

Upvotes: 3

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

You could use avg window function to do this.

select t.*,avg(qty) over(partition by monthnumber) as avg_qty_per_mnth
from tbl t

Upvotes: 4

Related Questions