CODEWITHSUNDEEP
sqlsql-servergroup-byaverage
milanDD
milanDD

Reputation: 133

SQL - AVG and Group by

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

Upvotes: 4

Views: 9396

Answers (4)

penguin
penguin

Reputation: 1376

Assuming every product value has a '.'(dot). Otherwise remove -1 from line 2.

SELECT Date,
   LEFT(CAST(product AS VARCHAR),charindex ('.',product) - 1),
   AVG(price)
FROM TABLE
GROUP BY Date, LEFT(CAST(product AS VARCHAR),charindex ('.',product) - 1);

Upvotes: 0

Prathamesh shinde
Prathamesh shinde

Reputation: 21

Try this:

     SELECT date,cast(product AS int) product,avg(Price) Price FROM (
     SELECT * FROM (VALUES 
     (cast('06-12-17' as date) ,    1.1      ,   10)
     ,(cast('06-12-17' as date) ,    1.2      ,   20)
     ,(cast('06-12-17' as date) ,    1.3      ,   30)
     ,(cast('06-12-17' as date) ,    1.4      ,   40)
     ,(cast('05-12-17' as date) ,    1.1      ,   20)
     ,(cast('05-12-17' as date) ,    1.2      ,   20)
     ,(cast('05-12-17' as date) ,    1.3      ,   40)
     ,(cast('05-12-17' as date) ,    1.4      ,   40)) a(Date      ,   
     Product   ,   Price ))x
     GROUP BY date,cast(product AS int)

Upvotes: 0

zarruq
zarruq

Reputation: 2465

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

Upvotes: 6

A.D.
A.D.

Reputation: 2372

There depending on which number you want as output. Like if

  • FLOOR() of every number
  • cast(product as int) convert to int

Query:

 SELECT
  Date,
  FLOOR(product) product, // This function can be replaced with above according to the output
  AVG(price) price

FROM your_table
GROUP BY date, FLOOR(product) order by Date

As you say that output as the error you can also try the following.

 SELECT
  Date,
  FLOOR(convert(float, product)) product, // This function can be replaced by FLOOR(cast(product as float))
  AVG(price) price

FROM your_table
GROUP BY date, FLOOR(convert(float, product)) order by Date;

Upvotes: 2

Related Questions