How do I derive a value for a column based on another column’s value?

I have a projects table like the one below. There are two slightly different id columns idx and idy–one idx can have multiple idys (for versioning).

  idx,     idy,         name,  use_count, usage_date
-----, -------, ------------, ----------, ----------
   x1,     x11,    x1 name 1,         10,          1
   x1,     x13,    x1 name 3,         10,          3
   x1,     x12,    x1 name 2,         10,          2
   x2,     x21,    x2 name 1,         10,          2
   x3,     x31,    x3 name 1,         10,          2

The goal is to consolidate the usage such that for every idx, the result will have:

• a use_count col which is SUM(use_count) for same idx
• the latest usage_date
• the name corresponding to the latest usage_date

Additionally, usage_date has to be within a certain range, but this hopefully is trivial.

  idx,     idy,         name,  use_count, usage_date
-----, -------, ------------, ----------, ----------
   x1,     x13,    x1 name 3,         30,          3
   x2,     x21,    x2 name 1,         10,          2
   x3,     x31,    x3 name 1,         10,          2

So far, I’ve got

SELECT
  idx, idy, name,
  sum(use_count) as use_count,
  usage_date
FROM
  projects
WHERE
  usage_date <= 3
GROUP BY
  idx

..but having trouble figuring out how to derive the latest name of the project (with id x1)

Answer

You can try below way –

DEMO

select X.idx,idy,name,X.use_count,usage_date from
(
select idx,sum(use_count) as use_count
from t
group by idx
)X inner join
(
select * from t a where usage_date = (select max(usage_date) from t b where a.idx=b.idx)
)Y on X.idx=Y.idx

Output:

idx idy   name       use_count  usage_date
x1  x13   x1 name 3     30            3
x2  x21   x2 name 1     10            2
x3  x31   x3 name 1     10            2