CODEWITHSUNDEEP
mysqlcount
uday
uday

Reputation: 183

MySql get Count, Value from 3rd Table with reference from 1st table

i would like to get the Count value from the 3rd table... 1st table has reference id to 2nd table, 2nd table has reference id of 3rd table... in 3rd table has the value... that i need to count... Table struct:

table1: tbl_rack

+------------+---------+--------+
| rack_id    | site_id | status |
+------------+---------+--------+
| R-642      | ST5     | Y      |
| R-307      | ST6     | Y      |
| R-57       | ST7     | Y      |
| 390/6      | ST8     | Y      |
| 9706       | ST11    | Y      |

table2: tbl_site

+---------+-------------+-----------+
| site_id | customer_id | region_id |
+---------+-------------+-----------+
| ST5    | CM8          | RM4       |
| ST6    | CM8          | RM8       |
| ST7    | CM10         | RM2       |
| ST8    | CM11         | RM12      |
| ST11   | CM8          | RM10      |

table3: tbl_customer

+-------------+----------------------+---------------+
| customer_id | customer_name        | customer_type |
+-------------+----------------------+---------------+
| CM8         | LIVI-IN              | MODERATE      |
| CM10        | PEPE                 | HIGH          |
| CM11        | SANDER               | LOW           |
| CM12        | TOASTER              | MODERATE      |

I want to count each customers contains how many Racks where ranks status is 'Y'

expected Result1:

Customer    No.of Racks
LIVI-IN     3
OTHERS      2

expected Result2:

Customer Type   No.of Racks
Moderate         3
High             1
Low              1

Upvotes: 1

Views: 26

Answers (1)

Vikram Jain
Vikram Jain

Reputation: 5588

Please, follow below SQL query:

select C.customer_name as 'Customer', count(*) as 'No.of Racks' 
from tbl_customer C
left outer join tbl_site TS on TS.customer_id = C.customer_id
left outer join tbl_rack TR on TR.site_id = TS.site_id
group by C.customer_name
order by  C.customer_name

Upvotes: 2

Related Questions