CODEWITHSUNDEEP
mysqlsqlcount
ivan
ivan

Reputation: 33

SQL Count with 3 tables

I need help with a sql select mixing the content of 3 tables, the tables are:

Clients
client_id   | name    | lastname
1           | Johan   | Doe
2           | Monique | Patricks

Orders
id_order  | client_id 
1001      | 1         
1002      | 1         
1003      | 2         

Order_lines
id_oline | id_order | id_product
1        | 1001     | 13
2        | 1001     | 21
3        | 1001     | 312
4        | 1002     | 33
5        | 1003     | 41
5        | 1003     | 11

I can do all the simple queries but I need one a little bit more difficult, that gives me this desired result: 

QUERY RESULT
client_id  | name     | lastname    | Num_products (Count rows)
1          | Johan    | Doe         | 4
2          | Monique  | Patricks    | 2

I don´t know even where to start... Any help will be preciated

Upvotes: 0

Views: 60

Answers (4)

Alex
Alex

Reputation: 17289

http://sqlfiddle.com/#!9/af49f1/1

SELECT c.client_id, c.name, c.lastname, 
       count(DISTINCT order_lines.id_product) as Num_Products
FROM Clients c
LEFT JOIN Orders o
ON c.client_id = o.client_id
LEFT JOIN Order_Lines 
ON o.id_order = Order_Lines.id_order
GROUP BY c.client_id

Upvotes: 2

Humba
Humba

Reputation: 128

SELECT C.client_id,name, lastname,COUNT(OL.id_order) as num_products FROM  Orders AS O 
INNER JOIN Order_lines AS OL ON O.id_order=OL.id_order 
INNER JOIN Clients AS C ON C.client_id=O.client_id
GROUP BY OL.id_order

Upvotes: 0

Michael Ziluck
Michael Ziluck

Reputation: 665

This code is completely untested, but it should do exactly what you need it to. I would recommend reading up on group by statements.

Hope this helps!

SELECT c.client_id, c.name, c.lastname, COUNT(*)
FROM Clients AS c
    INNER JOIN Orders AS o ON c.client_id = o.client_id
    INNER JOIN Order_lines AS ol ON o.id_order = ol.id_order
GROUP BY c.client_id, c.name, c.lastname;

Upvotes: 0

JNevill
JNevill

Reputation: 50218

For this you use aggregation with a GROUP BY clause:

SELECT client_id, name, lastname, count(order_lines.id_oline) as Num_Products
FROM Clients
    INNER JOIN Orders on clients.client_id = orders.order_id
    INNER JOIN Order_Lines on Orders.id_order = Order_Lines.id_order
GROUP BY client_id, name, lastname

Upvotes: 0

Related Questions