I have two tables in DB2, and I want to issue a single query for multiple rows in the 1st table, including one or more aggregate functions on relevant data in the 2nd table.
cust
cust_id | first_name | last_name
--------------------------------------
1 | jim | smith
2 | carol | angelo
3 | mike | jones
order
order_id | cust_id | amount
--------------------------------------
123 | 2 | 7.00
124 | 1 | 12.00
125 | 2 | 2.00
126 | 2 | 13.00
127 | 3 | 25.00
I want to query these tables and include specific columns from the cust table, while also including aggregates from the order table.
desired output for selecting only cust_id in (1, 2)
cust_id | first_name | last_name | order_count | order_total
--------------------------------------------------------------
1 | jim | smith | 1 | 12.00
2 | carol | angelo | 3 | 22.00
I know this has a simple solution but I don't understand if it involves Group By, or Join, or both... Thanks for helping with the query sql.
You can do it using JOIN to merge data from both tables, and GROUP BY to apply count() and sum() on the grouped data :
select c.cust_id, c.first_name, c.last_name, count(o.amount) as order_count , sum(o.amount) as order_total
from cust c
inner join orders o on o.cust_id = c.cust_id
where c.cust_id in (1,2)
group by c.cust_id, c.first_name, c.last_name