CODEWITHSUNDEEP
mysql
MidnightDataGeek
MidnightDataGeek

Reputation: 938

Find the Month with the Largest Profit in MYSQL

I have a table of daily profits and I want to find the month which made the largest total profit.

This is a sample of the base table

date        name              profit

2020-06-08  Voice Of Calm       7.5
2020-06-06  Pour Me A Drink     -1
2020-06-09  Sacred Dance        -1
2020-06-09  Paintball Wizard    -1
2020-06-09  Aljady               8
2020-06-09  Brian Epstein       -1
2020-06-09  Beautiful Illusion  -1
2020-06-09  Caravan Of Hope     -1
2020-06-09  Captain Corelli     -1.5
2020-06-09  Squelch              3.6
2020-06-09  Robeam              -1
2020-06-07  Midnights Legacy     5.6
2020-06-08  Tafish               -1
2020-06-09  What Will Be         6.45

There are multiple entries per day.

The output I am looking for would be the month which had the largest total profit. I know that Dec 2019 produced 122 points profit, therefore the output of the query would be:

Year  Month  Total Profit
2019  Dec    122

One thing which might cause an issue is where 2 months have the same profit, so April 2018 and 2019 both returned the same value. In this instance I would only want to return the latest month - April 2019

Any help is much appreciated

Upvotes: 0

Views: 51

Answers (1)

RusArtM
RusArtM

Reputation: 1300

Just count profit for each month, sort by summ of profit descending and take only the first row:

    SELECT DATE_FORMAT(date, "%Y-%m") month, SUM(profit) profit
    FROM table
    GROUP BY DATE_FORMAT(date, "%Y-%m")
    ORDER BY 2 DESC
    LIMIT 1

Upvotes: 1

Related Questions