CODEWITHSUNDEEP
mysqlsql
Alek Stephanok
Alek Stephanok

Reputation: 313

Sum price based on date and print based on clubs in mysql

I have two tables and those are football and football assets.

The Football looks like:-

id | pair | name | date | price | sell | fetched
---------------------------------------------------------------------------------
6    |  luca-antonelli  | Luca Antonelli | 2018-12-20 04:57:36 | 0.50 | 0.47 | 1
7    |  luca-antonelli  | Luca Antonelli | 2018-09-06 12:39:58 | 0.65 | 0.52 | 1
8    |  luca-antonelli  | Luca Antonelli | 2018-10-15 16:45:28 | 0.59 | 0.49 | 1
9    |  ryan-fraser     | Ryan Fraser    | 2018-03-28 08:56:57 | 5.51 | 0.47 | 1
10   |  ryan-fraser     | Ryan Fraser    | 2018-04-20 06:19:07 | 7.28 | 0.49 | 1
11   |  jordon-ibe      | Jordon Ibe     | 2018-10-14 22:52:58 | 11.23 | 0.17 | 1
12   |  jordon-ibe      | Jordon Ibe     | 2018-11-25 19:37:26 | 2.31 | 0.29 | 1

So you can see there are three rows so from here it will pick the price based on date The football assets looks like:-

    id | pair | Club
   -----------------------------------
    86 |  luca-antonelli  | Liverpool
    87 |  ryan-fraser     | Liverpool
    88 |  jordon-ibe      | Liverpool

So the club liverpool has 3 players. If you look into the football table you will see they have different prices. So it will pick the latest price based on datetime for each player and then add it up. pair is the common column between two tables. So there are many records like this. I want to sum the price of all the players of that club but the price will be the latest. price will take it from date column from football table. The output looks like:-

club | price
------------------
Liverpool | 28.07

The query I have tried is:-

SELECT club, SUM(price) as price from football JOIN football_assets ON football_assets.pair = football.pair WHERE club IN ('Paris Saint-Germain', 'Lille', 'Lyon')

One player have many prices but I want only their latest price to be summed. The above query is summing all the prices. I want that optimization.

Any help will be appreciated.

Upvotes: 0

Views: 41

Answers (1)

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

try like below by using subquery

select fa.club,sum(a.price) as price from

  (
  select f.* football f
   where f.date=(select max(date) from football f1 where f1.pair=f.pair)
  ) a join  footballassets fa on a.pair=fa.pair
  group by fa.club

By using correlated subquery i pick all the latest price then used join with assets table getting the club

Upvotes: 1

Related Questions