SQL - AVG and Group by

Question

I have the following table:

Date      |   Product   |   Price 
06-12-17  |    1.1      |   10
06-12-17  |    1.2      |   20
06-12-17  |    1.3      |   30
06-12-17  |    1.4      |   40
05-12-17  |    1.1      |   20
05-12-17  |    1.2      |   20
05-12-17  |    1.3      |   40
05-12-17  |    1.4      |   40

I am having hard time finding a query in SQL Server that can give me this result:

Date      |   Product |   Price 
06-12-17  |    1      |   25
05-12-17  |    1      |   30

I want the average price for each product everyday

Product starting from 1.1 to 24.4

Answer 1

If you just need left part of product, cast to int and then aggregate using resultant value and date.

select date, 
       cast(product as int) as product, 
       avg(price) as Price
from table1
group by date, cast(product as int)

Result:

date        product Price
--------------------------
05-12-17    1       30
06-12-17    1       25

DEMO

---

Update:

If product is of varchar datatype, use cast twice.

select date, 
       cast(cast(product as dec(3,1)) as int) as product, 
       avg(price) as Price
from table1
group by date, cast(cast(product as dec(3,1)) as int)

Varchar() Datatype DEMO