CODEWITHSUNDEEP
androidmysqlsqlsqlite
Jackson Chengalai
Jackson Chengalai

Reputation: 3937

group by month in sqlite

i have a table and i have little issue with groupby month 

CREATE TABLE BabyData (BabyId INT, BabyName TEXT, _id INT PRIMARY KEY, Date DATE, Height TEXT, Weight TEXT);

from this table i want the average of weight in each month in a year

when i am providing a date as '2011-2-2' then i want the average of weight in each month from '2011-2-2' to '2012-2-2'

i gave like this

SELECT strftime('%Y',Date) AS year, 
   strftime('%m',Date) AS month, 
   Avg(Weight) As Amount 
FROM BabyData 
Group By  strftime('%Y',Date),strftime('%m',Date)

But i getting only 1 average

Upvotes: 2

Views: 4278

Answers (1)

John Woo
John Woo

Reputation: 263843

SELECT strftime('%m',`Date`) AS `month`,
       Avg(Weight) As `Amount `
FROM BabyData
WHERE `DATE` BETWEEN DATE('2011-2-2') 
                       AND 
                     date('2011-2-2','+12 month')
GROUP BY `Month`

Upvotes: 5

Related Questions