Query a database and return a 'total' for each location based on a search parameter

I'm creating an API for a database, and I'm currently trying to make a search endpoint that takes the search parameter and returns all 'days' and a total of occurrences for that parameter. E.g. (Columns = degrees) (Rows = days in a year), if the parameter was 30 degrees I want to select the 30 degree column and return a total of occasions in which a day hit 30 degrees. So something like: Monday: 0:, Tuesday 10, Wednesday: 8, etc.

So far I've selected the data I want to use based on the parameter but I can't get it to sum each day. My attempts so far have either been a list of 365 days, or a sum of all the days. I understand that I would have to sum the Temp param but I can't work out how to do it so that I end up with each days total.

Current function:

router.get('/search/:Temp', func(req, res) {
    req.temps.from('days').select('weekDay', req.params.Temp)
    .then((columns)=> {
        res.json({'Search Result': rows)
    )

Solution

If I understand right, you have a table that looks something like this:

Id  Date        WeekDay     Temperature
----------------------------------------
1   2019-05-30  Thursday    25
2   2019-05-29  Wednesday   30
3   2019-05-28  Tuesday     30
4   2019-05-27  Monday      25
5   2019-05-26  Sunday      25
6   2019-05-25  Saturday    26
7   2019-05-24  Friday      27
8   2019-05-23  Thursday    25
9   2019-05-22  Wednesday   25
10  2019-05-21  Tuesday     30
11  2019-05-20  Monday      30
12  2019-05-19  Sunday      25
13  2019-05-18  Saturday    26
14  2019-05-17  Friday      25
15  2019-05-16  Thursday    25

And you want to have a result that looks something like this, given a temperature:

WeekDay     Occurences
-----------------------
Friday      1
Monday      1
Sunday      2
Thursday    3
Wednesday   1

You have to use the GROUP BY. In raw SQL it can be written like this:

SELECT
      WeekDay
    , COUNT(Temperature) AS Occurences
FROM Temperatures
WHERE Temperature = 25
GROUP BY WeekDay

The temperature here is 25. The GROUP BY clause groups all the weekdays together. When you group things together, you must also use aggregate functions that decides how the rows are grouped together. In this case, COUNT() is the aggregate function to use.

Using Knex, it should look something like this:

knex
  .select('WeekDay', knex.raw('COUNT(Temperature)'))
  .from('Temperatures')
  .where('Temperature', 25)
  .groupBy('WeekDay')