[FEATURE] log book entries assessment

[christianabila]

Is your feature request related to a problem? Please describe. No

Describe the solution you'd like The assessment should answer at least the following questions:

Number of ascents

• [ ] total (overall sum)
• [ ] per climb type
• [ ] per ascent type
• [ ] per climb type and ascent type
• [ ] per grade
• [ ] per grade and ascent type
• [ ] per wall type
• [ ] per grades and wall type

Grades

• [ ] highest grade climbed per climb type
• [ ] lowest grade climbed per climb type

• [ ] Grade with most ascents per climb type

  Climbing environment

• [ ] number of unique climbs indoors/outdoor
• [ ] number of unique climbs in different gyms
• [ ] number of unique climbs in outdoor crags and boulders

Progress

• [ ] climbing progress over time (x-axis: date, y-axis: climbing/boulder grade)

Describe alternatives you've considered Evaluation via LibreOffice

Additional context n/a

[christianabila]

Number of ascents - SQL queries

-- Total number of ascents
SELECT COUNT(id)
   FROM entry
WHERE ascenttype <> "Project"

-- Total number of ascents per climb type
     SELECT climbtype, COUNT(id)
        FROM entry
     WHERE ascenttype <> "Project"
GROUP BY climbtype

-- Total number of ascents per ascent type
     SELECT ascenttype, COUNT(id)
        FROM entry
     WHERE ascenttype <> "Project"
GROUP BY ascenttype

-- Total number of ascents per climb type and ascent type
     SELECT climbtype, ascenttype, COUNT(id)
        FROM entry
     WHERE ascenttype <> "Project"
GROUP BY climbtype, ascenttype

-- Total number of ascents per grade
     SELECT climbtype, grade, COUNT(id)
        FROM entry
     WHERE ascenttype <> "Project"
GROUP BY climbtype, grade

-- Total number of ascents per grade type and ascent type
     SELECT climbtype, grade, ascenttype, COUNT(id)
        FROM entry
     WHERE ascenttype <> "Project"
GROUP BY climbtype, grade, ascenttype

-- Total number of ascents per wall type
     SELECT walltype, COUNT(id)
        FROM entry
     WHERE ascenttype <> "Project"
GROUP BY walltype

-- Total number of ascents per grade and wall type
     SELECT gradeindex, grade, walltype, COUNT(id)
        FROM entry
     WHERE ascenttype <> "Project"
GROUP BY gradeindex, grade, walltype
ORDER BY gradeindex DESC

[christianabila]

Grades - SQL queries

-- Highest grade climbed per climb type
  SELECT climbtype, MAX(grade)
    FROM entry
GROUP BY climbtype

-- Lowest grade climbed per climb type
  SELECT climbtype, MIN(grade)
    FROM entry
GROUP BY climbtype

-- Most sent grade by climb type
SELECT a.climbtype, b.grade, a.total
 FROM
(SELECT totalspergrade.climbtype, MAX(totalspergrade.totals) total
          FROM (SELECT climbtype, grade, COUNT(grade) totals
                  FROM entry
                 WHERE ascenttype <> "Project"
              GROUP BY climbtype, grade) totalspergrade
GROUP BY totalspergrade.climbtype) a
JOIN (SELECT climbtype, grade, COUNT(grade) totals
                  FROM entry
                 WHERE ascenttype <> "Project"
              GROUP BY climbtype, grade) b ON a.climbtype = b.climbtype AND a.total = b.totals

[christianabila]

Climbing enviroments

-- Climbs per environment (indoor vs. outdoor)
SELECT entry.climbtype, label.name, COUNT(entry.id)
  FROM  entry entry
  JOIN entrylabels el ON el.entryid = entry.id
  JOIN label label ON label.id = el.labelid AND label.name IN ("Indoors", "Outdoors")
WHERE entry.ascenttype <> "Project"
GROUP BY entry.climbtype, label.id

-- Climbs per indoor climbing gym
SELECT entry.climbtype, label.name, COUNT(entry.id)
  FROM  entry entry
  JOIN entrylabels el ON el.entryid = entry.id
  JOIN label label ON label.id = el.labelid AND label.name IN ("BB Wienerberg", "BB Seestadt", "Blockfabrik", "Klettercenter Rotpunkt", "Marswiese", "Kletterhalle Wien", "Kletterhalle Südstadt")
WHERE entry.ascenttype <> "Project"
GROUP BY entry.climbtype, label.id