Group Information in joined table for last available year

Question

So I have this table with an unique identifier and its group; I want to get totals of another table based on its Group

Table 1

UNIQUE_ID ! Group

      1     West 
      2     West 
      3     West 
      4     West 
      5     West 
      6     East 
      7     East 

Then I have this second table from with which I join

Table 2

UNIQUE_ID ! NET PROFIT ! ASSETS ! EQUITY ! YEAR

       1     100           100     100     2016  
       1     100           100     100     2015  
       2     100           100     100     2016 
       2     100           100     100     2015  
       3     100           100     100     2016  
       3     100           100     100     2015  
    ***4     10            10      10      2015*** 
       5     100           100     100     2016  
       5     100           100     100     2015   
    ***6     10            10      10      2014*** 
       7     100           100     100     2016  
       7     100           100     100     2015
       7     100           100     100     2014

I link the previous tables and I group by Group which then gives me the totals for NEt profit, assets and equity, the problem is that it sums all the years available in table 2 Or I do a where year is 2016 and I only get the totals from 2016 not including the rows which id have the latest year of 2015 or 2014

I need it to group by Group and sum only the last available year for each unique ID, so that I would get the following table

Query would give me this table

Group ! NET PROFIT ! ASSETS ! EQUITY
 East      410        410      410
 West      110        110      110

Can anyone help me? I've looked everywhere and tried a number of combinations but without success

Answer 1

I. The complete SQL statement:

SELECT 
    ta.group,
    SUM(IFNULL(tb.net_profit, 0)) as sumNetProfit,
    SUM(IFNULL(tb.assets, 0)) as sumAssets,
    SUM(IFNULL(tb.equity, 0)) as sumEquity
FROM table_a AS ta
LEFT JOIN (
    SELECT 
        unique_id,
        max(year) as maxYear
    FROM table_b
    GROUP BY unique_id
) AS tbMaxYears ON tbMaxYears.unique_id = ta.unique_id
LEFT JOIN table_b AS tb ON 
    tb.unique_id = ta.unique_id
    AND tb.year = tbMaxYears.maxYear
GROUP BY ta.group;

II. Description:

The inner query:

SELECT 
    unique_id,
    max(year) as maxYear
FROM table_b
GROUP BY unique_id

• Selects the unique_ids from table_b and, for each unique_id, the corresponding latest year, e.g. the maximal year;
• Groups the fetched records by the unique_id;
• Is used in a LEFT JOIN statement under the alias maxYears and its results are filtered by the unique_ids fetched from the table_a.

The inner query results looks like following:

unique_id   maxYear
-------------------
1           2016
2           2016
3           2016
4           2015
5           2016
6           2014
7           2016

The outer query:

SELECT 
    ta.group,
    SUM(IFNULL(tb.net_profit, 0)) as sumNetProfit,
    SUM(IFNULL(tb.assets, 0)) as sumAssets,
    SUM(IFNULL(tb.equity, 0)) as sumEquity
FROM table_a AS ta
LEFT JOIN (
    <THE-INNER-QUERY-RESULTS>
) AS tbMaxYears ON tbMaxYears.unique_id = ta.unique_id
LEFT JOIN table_b AS tb ON 
    tb.unique_id = ta.unique_id
    AND tb.year = tbMaxYears.maxYear
GROUP BY ta.group;

• Reads all table_a records;
• Attaches the maxYears details (fetched through the inner query);
• Attaches the table_b details;
• Groups the records by the group column;
• Calculates the corresponding sums (including NULL values validation).

The results of the outer query (e.g. the final results):

group   sumNetProfit    sumAssets   sumEquity
---------------------------------------------
East    110             110         110
West    410             410         410

The second LEFT JOIN in the outer query:

LEFT JOIN table_b AS tb ON 
    tb.unique_id = ta.unique_id
    AND tb.year = tbMaxYears.maxYear

• Attaches (joins) the table_b details to the records fetched from table_a;
• Only the table_b records are attached, which have the same unique_id as the corresponding unique_id value from table_a AND (!) the same year as the corresponding maxYear value from maxYears table.

III. Used table structure:

I used a MySQL database with the following CREATE TABLE syntax:

CREATE TABLE `table_a` (
  `unique_id` int(11) DEFAULT NULL,
  `group` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `table_b` (
  `unique_id` int(11) DEFAULT NULL,
  `net_profit` int(11) DEFAULT NULL,
  `assets` int(11) DEFAULT NULL,
  `equity` int(11) DEFAULT NULL,
  `year` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I used the same data as you.

Good luck!

Answer 2

Consider a join of an aggregate derived table to the join of other two tables where essentially the last INNER JOIN acts as a WHERE clause to filter select years for each unique_id.

SELECT t1.`GROUP`, SUM(t2.NET_PROFIT) AS SUM_NET_PROFIT, 
                   SUM(t2.ASSETS) AS SUM_ASSETS, 
                   SUM(t2.EQUITY) AS SUM_EQUITY
FROM (`table2` t2
INNER JOIN `table1` t1
   ON t1.UNIQUE_ID = t2.UNIQUE_ID)       
INNER JOIN 
  (SELECT t2.UNIQUE_ID, MAX(t2.`YEAR`) AS MAX_YEAR
   FROM `table2` t2
   GROUP BY t2.UNIQUE_ID) g    
ON t2.`UNIQUE_ID` = g.`UNIQUE_ID` AND t2.`YEAR` = g.`MAX_YEAR`    
GROUP BY t1.`GROUP`;

Do note the parentheses used to wrap first join pairing of tables, required in MS Access.

Answer 3

Is this what you want?

SELECT Group_,
       SUM(NET_PROFIT) AS NET_PROFIT_YR,
       SUM(ASSETS) AS ASSETS_YR,
       SUM(EQUITY) As EQUITY_YR
  FROM Table1 AS T1
 INNER
  JOIN (SELECT T2_RAW.*
          FROM ( SELECT Unique_ID,
                        MAX(year) AS m_year
                   FROM Table2 AS T2
                  GROUP
                     BY Unique_ID
               ) AS MYR
         INNER
          JOIN Table2 AS T2_RAW
            ON MYR.unique_id = T2_RAW.unique_id
           AND MYR.m_year = T2_RAW.year
       ) AS TMP
    ON T1.unique_id = TMP.unique_id
 GROUP
    BY group_;