How to get different row counts of two different tables in one query with help of group by clause

Question

I have one Parent table(Order) and one child table(Item). One user can order multiple orders in one day and one order may have multiple Item.

My table structure like,

I would like to get result like, in one how many orders and Items have ordered by particular user.

I need result like

Thank you.

Answer 1

Try to use distinct count for the count columns, and grouping by user_id is enough as :

select o.user_id as "User Id",
       count(distinct o.id_order) as "Count(Order)",
       count(distinct i.id_item) as "Count(Item)",
       max("date") as "Date"
  from orders o
  join item i on o.id_order = i.id_order
 group by user_id;

User Id Count(Order)  Count(Item)    Date
------- ------------  -----------  ----------
   1         3            7        22.11.2018

Rextester Demo