Mysql – Consults and counts in multiple databases

MySQLsubquerysyntax

I'm not very familiar with mysql advanced queries, because I'm more of a designer as a developer.
My problem – I have 3 tables:

Clients

------------------------------------------------------
| id |        name        |          email           |
------------------------------------------------------ 
| 1  | Client 1           | client1@somecompany.com  |
| 2  | Client 2           | client2@somecompany.com  |
| 3  | Client 3           | client3@somecompany.com  |
| 4  | Client 4           | client4@somecompany.com  |
| 5  | Client 5           | client5@somecompany.com  |
| 6  | Client 6           | client6@somecompany.com  |
| 7  | Client 7           | client7@somecompany.com  |
| 8  | Client 8           | client8@somecompany.com  |
| 9  | Client 9           | client9@somecompany.com  |
| 10 | Client 10          | client10@somecompany.com |
------------------------------------------------------


Products
--------------------------------
| id | client_id |  item_name  |
--------------------------------
| 1  | 6         |  something  |
| 2  | 4         |  something  |
| 3  | 6         |  something  |
| 4  | 2         |  something  |
| 5  | 5         |  something  |
| 6  | 7         |  something  |
| 7  | 9         |  something  |
| 8  | 10        |  something  |
| 9  | 1         |  something  |
| 10 | 5         |  something  |
| 11 | 8         |  something  |
--------------------------------

product_status

------------------------------
| id | product_id |  status  |
------------------------------
| 1  | 1          | new      |
| 2  | 2          | used     |
| 3  | 3          | new      |
| 4  | 4          | used     |
| 5  | 5          | used     |
| 6  | 6          | new      |
| 7  | 7          | used     |
| 8  | 8          | used     |
| 9  | 9          | new      |
| 10 | 10         | used     |
| 11 | 11         | new      |
------------------------------

I need know the following:

  • Id and name of client

  • How many items has the client

  • How many items "new" and "used"

Like this 

expected results
-----------------------------------------------------------------
| client_id | client_name | total_items | Items_new | Item_used |
-----------------------------------------------------------------
| 1         | Client 1    | 1           | 1         | 0         |
| 2         | Client 2    | 1           | 0         | 1         |
| 3         | Client 3    | 1           | 0         | 1         |
| 4         | Client 4    | 1           | 0         | 1         |
| 5         | Client 5    | 1           | 0         | 1         |
| 6         | Client 6    | 2           | 2         | 0         |
| 7         | Client 7    | 1           | 0         | 1         |
| 8         | Client 8    | 1           | 1         | 0         |
| 9         | Client 9    | 1           | 0         | 1         |
| 10        | Client 10   | 1           | 0         | 1         |
----------------------------------------------------------------

How can I achieve this?
Thanks for your help!

Best Answer

You can join the tables and then use (conditional) aggregation.

SELECT c.id client_id,
       c.name client_name,
       count(p.id) total_items,
       count(CASE
               WHEN ps.status = 'new'
                 1
             END) items_new,
       count(CASE
               WHEN ps.status = 'used'
                 1
             END) items_used
       FROM clients c
            LEFT JOIN products p
                      ON p.client_id = c.id
            LEFT JOIN product_status ps
                      ON ps.product_id = p.id
       GROUP BY c.id,
                c.name;

This shows data for all clients. If you only want them actually having a product, you need to change the first LEFT JOIN to INNER JOIN.

It also counts products which have no status in items. If you don't want to include such products, change the second LEFT JOIN to INNER JOIN as well.

Related Question