SQL Query agregate for date

Question

I want to build a query to aggregate two tables to get a value of all max(date) of all component_id of one project_id

component

id  project_id          
200 100         
201 100         

component_meta

id  component_id    value   date    
1   200             1000    2018-01-01  (this component_id is older)
2   200             1200    2018-01-02  
3   201             2000    2018-01-02  

Query

SELECT … FROM … WHERE project_id = 100  

should be Result :

value               
3200    

I tested with INNER JOIN but most of I get a Invalid Error (...aggregate function or the GROUP BY clause)

Many many thanks for helping!!

Answer 1

I would suggest a correlated subquery to get the most recent component value and then aggregation:

select sum(cm2.value)
from component c join
     component_meta cm
     on cm.component_id = c.id
where c.project_id = 100 and
      cm.date = (select max(cm2.date)
                 from component_meta cm2
                 where cm2.component_id = cm.component_id
                );

For optimal performance, you want indexes on component(project_id, id) and component_meta(component_id, date).

Answer 2

I'm doing this strictly from memory, but based on the information you've provided it should be something like this:

SELECT SUM(component_meta.value)
FROM component 
INNER JOIN component_meta ON component.id = component_meta.component_id
WHERE component.project_id = 100;

Note, I'm also doing my best to interpret what you're asking for. You said the desired result is 3200 and I'm assuming that's from summing the component_meta values for project ID 100. Please let me know if this is misinterpreted.

If you want only the summed meta values matching the max date it should be something like:

SELECT SUM(component_meta.value)
FROM component 
INNER JOIN component_meta ON component.id = component_meta.component_id
WHERE component.project_id = 100
HAVING MAX(component_meta.date);