SQL Count Percentage from Multiple Tables with Where clause

Question

I have three tables:
Table Clothes

id	clothname	supplier	instock
1	Shirt	Venom	Yes
2	Tshirt	Traders	No

Table Toys

id	toyname	instock
1	Car	Yes
2	Ball	Yes
2	Yoyo	N/A

Table Tools

id	toolname	instock
1	Drill	Yes
2	Hammer	No

I would like single SQL query to count percentage of all products from 3 tables, where Instock = Yes
From table Clothes there are two conditions, instock = Yes and supplier = Venom.

Desired output would be: Percentage of products in stock: XY% percent

| Status   | Percentage from total Products  |
+----------+---------------------------------+
| Instock  |              57.14              |

What would be the SQL query for this?

Answer 1

Your data

CREATE TABLE Clothes (
  id INTEGER NOT NULL, 
  clothname VARCHAR(70) NOT NULL, 
  supplier VARCHAR(80) NOT NULL, 
  instock VARCHAR(30) NOT NULL
);
INSERT INTO Clothes(id, clothname, supplier, instock) 
VALUES 
  (1, 'Shirt', 'Venom', 'Yes'), 
  (2, 'Tshirt', 'Traders', 'No');
CREATE TABLE Toys (
  id INTEGER NOT NULL, 
  toyname VARCHAR(50) NOT NULL, 
  instock VARCHAR(30) NOT NULL
);
INSERT INTO Toys(id, toyname, instock) 
VALUES 
  (1, 'Car', 'Yes'), 
  (2, 'Ball', 'Yes'), 
  (2, 'Yoyo', 'N/A');
CREATE TABLE Tools (
  id INTEGER NOT NULL, 
  toyname VARCHAR(50) NOT NULL, 
  instock VARCHAR(30) NOT NULL
);
INSERT INTO Tools(id, toyname, instock) 
VALUES 
  (1, 'Drill', 'Yes'), 
  (2, 'Hammer', 'No');

Use union all and cte as follows

WITH T AS
(
       SELECT id,
              clothname,
              instock
       FROM   Clothes
       WHERE  supplier='Venom'
       UNION ALL
       SELECT *
       FROM   Toys
       UNION ALL
       SELECT *
       FROM   Tools)

SELECT Instock                              AS status,
       Cast(Count(*) AS FLOAT) / (SELECT Count(*)
                                  FROM   T) AS 'Percentage from total Products'
FROM   T
WHERE  Instock = 'Yes'
GROUP  BY Instock   

desired output

status	Percentage from total Products
Yes	0.666666666666667