CODEWITHSUNDEEP
sqlite
Relax ZeroC
Relax ZeroC

Reputation: 683

How sqlite to do a range of avg by different group

I have a table and value like ,

        name    date          score
----------------------------------------
1.      Mary   2017-03-01     14
2.      John   2017-03-01     22
3.      Tom    2017-03-01     6
4.      Mary   2017-03-02     35
5.      John   2017-03-02     17
6.      Tom    2017-03-02     50
7.      Mary   2017-03-03     90
8.      John   2017-03-03     88
9.      Tom    2017-03-03     12

I want to get score avg from 2017-03-03 ~ 2017-03-02 and name as group (Mary,John,Tom) so, I want the execute ans is ,

        name    score
----------------------------------------
1.      Mary    62.5
2.      John    52.5
3.      Tom     31

How can I do this ? Thanks.

Upvotes: 0

Views: 27

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521944

I think you just need a basic GROUP BY query with a WHERE clause which restricts to the dates of interest.

SELECT name,
       AVG(score) AS score
FROM yourTable
WHERE date BETWEEN '2017-03-02' AND '2017-03-03'
GROUP BY name

Upvotes: 1

Related Questions