-1

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
sql101
  • 3
  • 1
  • 2
    Assuming that you already learned about [aggregation](https://dev.mysql.com/doc/refman/8.0/en/group-by-functions.html) and [joins](https://dev.mysql.com/doc/refman/8.0/en/join.html), just start writing it. – PM 77-1 Jun 09 '20 at 20:32
  • See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query – Strawberry Jun 09 '20 at 20:48

1 Answers1

0

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

Yair I
  • 1,133
  • 1
  • 6
  • 9