1

In my database I have two tables:

type_of_activity

CREATE TABLE IF NOT EXISTS type_of_activity(id_type INTEGER PRIMARY KEY, name VARCHAR(20))

activity

CREATE TABLE IF NOT EXISTS activity(
 id_activity INTEGER PRIMARY KEY AUTOINCREMENT,
 id_type INTEGER,
 start_time TEXT,
 distance REAL,
 duration INTEGER,
 elevation INTEGER,
 FOREIGN KEY(id_type) REFERENCES type_of_activity(id_type))

Some details: start_time format: "YYYY-MM-DD HH:MM", duration in seconds. I want to make query that summary:

  1. Every week mileage, duration, maybe from this data calculate pace, and elevation.
  2. The same as above, but for months

I think that is good to convert the start_time to: in 1)strftime('%Y-%W',start_time); 2)strftime('%Y-%m',start_time) But I don't know how to aggregate results.

Can someone help me?

0

I made it

SELECT 
  strftime('%Y-%m',star_time) AS date
  , Sum(distance) 
FROM activity 
WHERE 
Group By 
  strftime('%Y-%m',start_time)
  • But I still don't know how to calculate pace in this select ;( – Kuba Ronkiewicz Nov 9 '16 at 21:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.