Search code examples
sqlgroup-byaveragenumericpartition

Where to place numeric in this SQL SELECT query

I'm wondering where the correct placement for NUMERIC(5,2) in relation to my avg is in my SQL query below:

select distinct 
    b.name, avg(c.price) over (partition by b.name) as average_price
from     
    Catalog a 
join 
    books b on (a.book_id = b.id)
join 
    movies c on (a.movie_id = c.id)
where    
    c.price is not null 
    and a.record >= 2
group by 
    b.name, c.price, average_price

I have tried what feels like everywhere!

Thanks in advance.

Solution

  • I would just cast the average:

    select distinct b.name,
       cast(avg(c.price) over (partition by b.name) as numeric(5,2)) as average_price
from ...

    This assumes that you want the average calculation to proceed using whatever original precision was in the price column, and you only want to view the output as numeric(5,2).