Qfyilyi

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.

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

You have to use INNER JOIN for user,order and item table. also use GROUP BY clause for group each date and order.

SELECT user_id,

       count(o.id_order),

       count(item.id_item)

FROM USER

INNER JOIN

`ORDER` o ON user.id=o.id_order

INNER JOIN item i ON o.id_order=o.id_order

GROUP BY o.Date, o.id_order