SQL Aggregation of primary keys-CodePudding

So I have two tables in my sample database:

Table #1:

PRODUCT_ID     TYPE    DATE    CONSUMER_ID

where PRODUCT_ID is the primary key.

Table #2:

PRODUCT_ID     ORDER_DATE    SENT     RETURNED

which contains possible duplicates of PRODUCT_ID

I want a query which returns :

CONSUMER_ID   TOTAL_PRODUCTS   TOTAL_SENT   TOTAL_RETURNED

Since I am fairly new to SQL, I cannot seem to join the two tables to get the meaningful data so any insight on this will be highly appreciated.

CodePudding user response：

select consumer_id, count(t2.product_id) TOTAL_PRODUCTS, sum(sent) TOTAL_SENT, sum(returned) TOTAL_RETURNED
from table1 t1 join table2 t2
on t1.product_id = t2.product_id
group by consumer_id