CODEWITHSUNDEEP
sqlpostgresqljoingroup-bysum
Roby Sottini
Roby Sottini

Reputation: 2265

SQL: How to group by with two tables?

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:

SQL result

Upvotes: 2

Views: 27061

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

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.

Upvotes: 3

Jason W
Jason W

Reputation: 13179

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;

Upvotes: 2

Related Questions