[SOLVED] Need to derive count of occurrence in Table 2 based on the results of Occurrence in Table 1 using sql query

Need to derive count of occurrence in Table 2 based on the results of Occurrence in Table 1 using sql query

Table_1 has order_id, country_id details

table_ID   order_id   country_id
1          100        IN
2          200        USA
3          300        UK
4          400        IN
5          500        UK
6          600        UK
7          700        USA
8          800        USA
9          900        IN
10         1000       UK

Table_2 has shipment_id, order_id details

Shipment_ID   order_id   
1             100        
2             100        
3             100        
4             200        
5             200        
6             300        
7             300        
8             400        
9             500        
11            500
12            600
13            700
14            700
15            700
16            700
17            800
18            800
19            800
20            900
21            900
22            1000
23            1000
24            1000

I used the following query to find out list of order_id which are for country_id='IN'

select `order_id`
from `Table_1` 
where `country_id` = 'IN'; 

order_id
100
400
900

I need guidance to write the query to find the count of shipment_id which will are mapped to order_id from 'IN'

So order_id 100 has 3 shipment, 400 has 1 and 900 has 2 shipment

Desired final output

count_of_shipment_id
6

Solution

here is the query you need:

SELECT country_id, count(*) as count_of_shipment_id
FROM Table_1 a
inner join Table_2 b on a.`order_id` = b.`order_id`
group by country_id

if you need only one country you can always add "where" or "having" to filter the result.

here you can see the sample you posted: http://sqlfiddle.com/#!9/c90424/2