SQL: How to group by with two tables?

Question

I have the tables products and history and I need to group by name:

products = (id_product, name)

history = (id_history, id_product, amount)

I tried this SQL query but it isn't grouped by name:

SELECT
      products.name,
      sum(history.amount)
FROM history
INNER JOIN products ON history.id_product = products.id_product
GROUP BY
      products.name,
      history.amount,
      history.id_history;

This is the result:

Solution 1

You should only be grouping by the attributes you need to be aggregated. In this case, you need only products.name.

SELECT
      products.name,
      sum(history.amount) AS [Amount]
FROM history
INNER JOIN products ON history.id_product = products.id_product
GROUP BY
      products.name;

If you need to include products without history (assuming sum should be 0 instead of null in this case), then you can use an OUTER JOIN instead of INNER JOIN to include all products:

SELECT
      products.name,
      COALESCE(sum(history.amount), 0) AS [Amount]
FROM history
    RIGHT OUTER JOIN products ON history.id_product = products.id_product
GROUP BY
      products.name;

Solution 2

This is no answer, but too long for a comment.

For readability's sake the product table should be first. After all it is products that we select from, plus a history sum that we can access via [left] join history ... followed by an aggregation, or [left] join (<history aggregation query>), or a subselect in the select clause.

Another step to enhance readability is the use of alias names.

Join the table, then aggregate

select p.name, coalesce(sum(h.amount), 0) as total
from products p
left join history h on h.id_product = p.id_product
group by p.name
order by p.name;

Aggregate, then join

select p.name, coalesce(h.sum_amount, 0) as total
from products p
left join
(
  select sum(h.amount) as sum_amount
  from history 
  group by id_product
) h on h.id_product = p.id_product
order by p.name;

Get the sum in the select clause

select
  name,
  (select sum(amount) from history h where h.id_product = p.id_product) as total
from products p
order by p.name;

And as you were confused on how to use GROUP BY, here is an explanation: GROUP BY ___ means you want one result row per ___. In your original query you had GROUP BY products.name, history.amount, history.id_history saying you wanted one result row per name, amount, and id, while you actually wanted one row per name only, i.e. GROUP BY products.name.

Comments

[Jason W]

You just need to group by products.name - not the history fields

[Lamak]

just remove history.amount and history.id_history from the GROUP BY

[jarlh]

And if you do product LEFT JOIN history, your result will also include products without a history.

[a_horse_with_no_name]

Which DBMS product are you using? "SQL" is just a query language, not the name of a specific database product. Please add a tag for the database product you are using postgresql, oracle, sql-server, db2, ...

[Roby Sottini]

I need the name and the amount too.

[Jason W]

This should return 2 columns: "name" and "amount". The SELECT controls what is returned, The GROUP BY controls how the SUM groups its calculation.

[jarlh]

I'd do products left join history, because most people find it much easier to understand main table left join optional data instead of optional data right join main table.

[Jason W]

I do as well @jarlh, but just used right join in this case since OP had already specified that order. It is probably first time I've done a right join in years :)