CODEWITHSUNDEEP
mysql
JJRhythm
JJRhythm

Reputation: 643

Quering the most recent columns and summing the others in MySQL

I'm trying to make a query that would do something like this...

id | id2 | profit | expense | status | date
1     1      10       20      Active  7-3-11
1     1      11        5      Not Act 7-4-11
1     2      5        10      Active  7-4-11

I want to group it by Id, and Id2 sum the profit and expense columns, then return the most recent status, like so.

id | id2 | profit | expense | status
1     1      22       25      Not Act
1     2      5        10      Active

What I've tried so far is missing a large chunk of the data it's supposed to have and mostly when the id is null but not always.

SELECT * FROM
    tbl
  JOIN
    (SELECT
       id,
       id2,
       max(date) maxdate
     FROM
       tbl
     GROUP BY
       id,
       id2) sub
  ON tbl.id = sub.id AND
     tbl.id2 = sub.id2 AND
     tbl.date = sub.maxdate;

Any help would be great!

Upvotes: 1

Views: 40

Answers (1)

CristiC
CristiC

Reputation: 22698

SELECT
       id,
       id2,
       sum(profit) profit,
       sum(expense) expense,
       (select status from tbl t1 where t1.id=tbl.id and t1.id2=tbl.id2 order by date desc limit 1)
     FROM
       tbl
     GROUP BY
       id,
       id2

Upvotes: 1

Related Questions