-1

I need to calculate an average price for the latest date for every agriculture. I use this Google query to load table from Google Sheets.

var queryString = encodeURIComponent("select max(A), D, avg(E) where B = 'cpt-novo' group by D");

But i get incorrect result. Also, if i change anything, i often get errors such as "ADD_COL_TO_GROUP_BY_OR_AGG" or "CANNOT_GROUP_WITHOUT_AGG".

Here's the table:

date,       basis,    trader,   culture,       price
10.10.2017, cpt-novo, one,      wheat-5-class, 8000
31.10.2017, cpt-novo, one,      wheat-5-class, 8500
17.10.2017, cpt-novo, one,      wheat-11.5,    9100
31.10.2017, cpt-novo, one,      wheat-11.5,    9200
17.10.2017, cpt-novo, one,      wheat-12,      9300
31.10.2017, cpt-novo, one,      wheat-12,      9400
17.10.2017, cpt-novo, one,      wheat-12.5,    9500
31.10.2017, cpt-novo, one,      wheat-12.5,    9600
17.10.2017, cpt-novo, one,      wheat-13,      9750
31.10.2017, cpt-novo, one,      wheat-13,      9850
17.10.2017, cpt-novo, one,      wheat-13.5,    10000
31.10.2017, cpt-novo, one,      wheat-13.5,    10100
27.07.2017, cpt-novo, two,      barley,        8600
05.08.2017, cpt-novo, two,      barley,        9000
02.09.2017, cpt-novo, two,      wheat-11.5,    8300
10.10.2017, cpt-novo, two,      wheat-11.5,    9000
10.10.2017, cpt-novo, two,      wheat-12,      9300
01.12.2017, cpt-novo, two,      wheat-12,      9200
10.10.2017, cpt-novo, two,      wheat-12.5,    9600
01.12.2017, cpt-novo, two,      wheat-12.5,    9500
10.10.2017, cpt-novo, two,      wheat-13,      9800
01.12.2017, cpt-novo, two,      wheat-13,      9700
10.10.2017, cpt-novo, two,      wheat-13.5,    10000
10.10.2017, cpt-novo, two,      wheat-13.5,    10100
06.12.2017, cpt-novo, three,    wheat-13,      9800
06.12.2017, cpt-novo, three,    wheat-12,      9400
06.12.2017, cpt-novo, three,    wheat-11.5,    9200

The results should be these (calculated myself):

wheat-5-class,  8500
wheat-11.5,     9133,333333
wheat-12,       9333,333333
wheat-12.5,     9550
wheat-13,       9783,333333
wheat-13.5,     10100
barley,         9000
randomsuffer
  • 353
  • 2
  • 7
  • 15

1 Answers1

0

This answer is for MySQL:

Step one: Calculate the MAX(date) for each culture...

    SELECT culture, MAX(date) max_date FROM my_table GROUP BY culture;
    +---------------+------------+
    | culture       | max_date   |
    +---------------+------------+
    | barley        | 2017-08-05 |
    | wheat-11.5    | 2017-10-31 |
    | wheat-12      | 2017-12-01 |
    | wheat-12.5    | 2017-12-01 |
    | wheat-13      | 2017-12-01 |
    | wheat-13.5    | 2017-10-31 |
    | wheat-5-class | 2017-10-31 |
    +---------------+------------+

Step Two: Find all rows which satisfy the above...

SELECT a.* 
  FROM my_table a 
  JOIN
     ( SELECT culture
            , MAX(date) max_date 
         FROM my_table 
        GROUP 
           BY culture
     ) b
    ON b.culture = a.culture
   AND b.max_date = a.date;
+------------+----------+--------+---------------+-------+
| Date       | basis    | trader | culture       | price |
+------------+----------+--------+---------------+-------+
| 2017-08-05 | cpt-novo | two    | barley        |  9000 |
| 2017-10-31 | cpt-novo | one    | wheat-11.5    |  9200 |
| 2017-12-01 | cpt-novo | two    | wheat-12      |  9200 |
| 2017-12-01 | cpt-novo | two    | wheat-12.5    |  9500 |
| 2017-12-01 | cpt-novo | two    | wheat-13      |  9700 |
| 2017-10-31 | cpt-novo | one    | wheat-13.5    | 10100 |
| 2017-10-31 | cpt-novo | one    | wheat-5-class |  8500 |
+------------+----------+--------+---------------+-------+

Step Three: Find the average price (by culture) for the above.

Left as an exercise for the reader.

EDIT: I don't know anything about gql. I suspect that you can write something like the following, but I've no idea if it's correct or efficient...

for b in db.GqlQuery("SELECT culture , MAX(date) max_date GROUP BY culture"):
for a in db.GqlQuery("SELECT * WHERE culture=:1 AND date=:2", b.culture,b.max_date):
print a.price

Again, in the unlikely event that the above is correct, I've omitted Step 3.

Strawberry
  • 33,750
  • 13
  • 40
  • 57