SQL query modification!

Transact-SQL
1

Hi friends
I have 4 tables as below.

Products (prodid, prodname)

Suppliers (supplierid, suppliername, prodid)

customers (customerid, customername, orderid, supplierid)

orders (orderid, ordername)

I want to retrieve Supplier records whose prodid is = "prod1" and count (customers) whose name = "sample customer" and count(orders) whose ordername = "some order" for that supplier.

Output I want is:

All suppliers who's prodid is prodid1 and count of customers n count of orders.

I tried below query:

select s.supplierid, s.suppliername,

(Select count(customerid) where c.supplierid = s.supplierid) as customercount,

(Select count(orderid) where o.orderid = c.orderid) as ordercount

From suppliers as s

Inner join customers c on c.supplierid = s.supplierid

Inner join orders o on o.orderid = c.orderid

Where s.productid = "prodid1"

But my query giving wrong result and duplicate rows.

Please help in correcting this or suggestions new?

2

Please provide dml and ddl

3 
create table Products(prodid varchar(50), prodname varchar(50))
insert into Products
select 'ProdId1', 'product1' union
select 'ProdId2', 'product2'

create table Suppliers(SupplierId varchar(50), suppliername varchar(50), prodid varchar(50))

insert into Suppliers
select 'SupplierId1', 'first supplier', 'ProdId1' union
select 'SupplierId2', 'some supplier', 'ProdId1'

create table Customers(SupplierId varchar(50), customerid varchar(50), customername varchar(50), ordered varchar(50))

insert into Customers
select 'SupplierId1', 'customerid1', 'my name', 'order1' union
select 'SupplierId2', 'customerid2', 'my name'
, 'order2' union

select 'SupplierId1', 'customerid3', 'my name'
, 'order1'

create table orders(orderid varchar(50), ordername varchar(50))

insert into orders
select 'order1', 'my order' union
select 'order2', 'your order'
4

Please find the DML statements for quick check.