0

So I have table 'item' in database with attributes: id, dateItem, price.

I want to find MAX(SUM(price)) for some month in a current year.

id  dateItem (Format: yyyy-mm-dd)    price
1   25.06.2015.                      986,69
2   21.06.2015.                      1564
3   22.03.2015.                      23,56
4   21.03.2015.                      187,23
5   01.03.2015.                      489,33
6   06.10.2015.                      975,26

I came up with something like this, but I know it's not ok. Please, help :s

$sql = "SELECT MAX(SUM(price)) FROM item WHERE DATE('Y') = 2015 AND 
dateItem between DATE('Y/m/1') and DATE('Y/m/31')";
KuKeC
  • 4,392
  • 5
  • 31
  • 60
xKRgirl01
  • 41
  • 1
  • 6
  • 1
    Is that really the format of you date field (a string?) or is it really a `date` field type? So you want monthly sum and you want to find the highest of those? – Mike Brant Oct 21 '15 at 19:14
  • You can just do `ORDER BY price DESC LIMIT 1` – rjdown Oct 21 '15 at 19:15
  • 1
    What do you mean by `MAX(SUM())` when no `GROUP` is in use? you have only one `SUM` maximum among what? – Alex Oct 21 '15 at 19:16
  • Possible duplicate of [Group by month and year in MySQL](http://stackoverflow.com/questions/3366895/group-by-month-and-year-in-mysql) – chris85 Oct 21 '15 at 19:17

2 Answers2

2

You can't nest aggregation functions. You need to use a subquery.

SELECT MAX(pricesum) 
FROM (SELECT SUM(price) AS pricesum
      FROM item
      WHERE YEAR(dateItem) = YEAR(NOW())
      GROUP BY MONTH(dateItem)) AS subquery
Barmar
  • 741,623
  • 53
  • 500
  • 612
0

You can do this with ORDER BY and LIMIT:

SELECT SUM(price) AS pricesum
FROM item
WHERE YEAR(dateItem) = YEAR(NOW())
GROUP BY MONTH(dateItem)
ORDER BY pricesum DESC
LIMIT 1;

If you want to know the month as well, you can include that in the SELECT clause.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786