CODEWITHSUNDEEP
mysqlsql
reve berces
reve berces

Reputation: 349

select the highest/greatest sum group by date

I have a table and I want to take the sum of the columns and get the result with the highest/greatest sum

this is my table

sales
+-------------+----------+--------------------------------+
| package_id  |  amount  |          datesales             |
+-------------+----------+--------------------------------+
|     123     |    100   |  2019-12-01 09:18:19.000000    |
|     123     |    100   |  2019-12-01 09:18:19.000000    |
|     123     |    100   |  2019-12-02 09:18:19.000000    |
|     123     |    200   |  2019-12-02 09:18:19.000000    |
+-------------+----------+--------------------------------+

datesales column is in timestamp

this is my query

SELECT max(AMOUNTSUM) AS amount42
                    FROM
                    (
                        SELECT SUM(AMOUNT) AS AMOUNTSUM
                        FROM sales
                        where date(datesales) <= curdate() and
                        date(datesales) >= curdate() - interval 6 day
                        and package_id = 123
                        group by datesales
                    )

i have 4 entries and the dates are today and yesterday, so I was expecting to get highest/greatest sum based on the datesales column which is in timestamp

the datesales(today) has a sum of 200 while the datesales(yesterday) has 300, I want to get the 300 result and I don't know what is wrong with my query

EXPECTED RESULT

+-----------+-------+
| yesterday |  300  |
+-----------+-------+

THE RESULT I GET

+-----------+-------+
| yesterday |  200  |
+-----------+-------+

Upvotes: 0

Views: 47

Answers (3)

spencer7593
spencer7593

Reputation: 108420

I would do it like this:

SELECT MAX(v.amountsum) AS amount42
  FROM (
         SELECT DATE(s.datesales)  AS sales_dt
              , SUM(s.amount)      AS amountsum
           FROM sales s
          WHERE s.package_id   = 123
            AND s.datesales   >= CURDATE() + INTERVAL -6 DAY
            AND s.datesales    < CURDATE() + INTERVAL +1 DAY
          GROUP
             BY DATE(s.datesales)
       ) v

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270011

You need to aggregate by the date, not the date/time. You can also eliminate the subquery:

select sum(AMOUNT) AS AMOUNTSUM
from sales
where datesales < curdate() + interval 1 day and
      datesales >= curdate() - interval 6 day and
      package_id = 123
group by date(datesales)
order by amountsum desc
limit 1;

Note that I changed the date comparisons so they are index-friendly.

Upvotes: 3

alex067
alex067

Reputation: 3281

Since you're grouping by the datesales, you don't need to SUM the amount, because the group by will SUM the amount anyways.

Remove the SUM(AMOUNT) and just select the amount.

 SELECT amount
 FROM sales
 where date(datesales) <= curdate() and
 date(datesales) >= curdate() - interval 6 day 
 and package_id = 123
 group by datesales

Upvotes: 0

Related Questions