Left outer joins aggregate first-CodePudding

I have the following tables

CREATE TABLE categories(
  id SERIAL,
);

CREATE TABLE category_translations(
  id SERIAL,
  name varchar not null,
  locale varchar not null, 
  category_id integer not null
);

CREATE TABLE products(
  id SERIAL,
  category_id integer not null
);

CREATE TABLE line_items(
  id SERIAL,
  total_cents integer
  product_id integer not null
);

What I'm trying to do is output is a map of each category name to the sum of total of its associated line_items total_cents. Something like:

| name          | sum_total_cents  |
|---------------|------------------|
| Fresh foods   |           100000 |
| Dry products  |           532000 |

There is a uniqueness constraint that only one name for each locale will be stored. So a category will have one row for each locale stored in the category_translations table

What I currently have is

SELECT SUM(line_items.total_cents) AS sum_total_cents, ???
FROM line_items INNER JOIN products ON products.id = line_items.product_id
INNER JOIN categories ON categories.id = products.category_id
LEFT OUTER JOIN category_translations ON category_translations.category_id = categories.id 
WHERE category_translations.locale ='en'
GROUP BY categories.id

I'm looking for an aggregate function to return the first name for the category. The only piece missing is that what to be written instead of the ??? as I've been facing a lot of must appear in the GROUP BY clause or be used in an aggregate function errors. In pseudo-code I'm looking for a FIRST() aggregate method in PostgreSQL that I can use

CodePudding user response：

Assuming you want one random name from any locale, you can do:

select
  c.id,
  (select name from category_translations t 
   where t.category_id = c.id limit 1) as name,
  sum(i.total_cents) as sum_total_cents
from categories c
left join products p on p.category_id = c.id
left join line_items i on i.product_id = p.id
group by c.id, name

Alternatively, if you want the category name for the locale 'en' then you can do:

select
  c.id,
  (select t.name from category_translations t 
   where t.category_id = c.id and t.locale ='en') as name,
  sum(i.total_cents) as sum_total_cents
from categories c
left join products p on p.category_id = c.id
left join line_items i on i.product_id = p.id
group by c.id, name