CODEWITHSUNDEEP
sqlsql-servercount
Loic
Loic

Reputation: 123

One SQL Query to get number of items from different tables reference in another table

I have 5 tables which are

- Project(PK Project_ID), 
- Bottles (PK Bottle_ID, FK Project_ID), 
- Plastics (PK Plastic_ID, FK Project_ID), 
- Glasses (PK Glass_ID,FK Project_ID) and 
- Cups (PK Cup_ID, FK Project_ID)

How can I get one SQL query to return the number of Bottles ,Plastics , Glasses and Cup per Project, where One Project has many Bottles ,Plastics , Glasses and Cup ?

Upvotes: 1

Views: 127

Answers (4)

Premks
Premks

Reputation: 21

Create table Project (Project_ID int )
Create table Bottles ( Bottle_ID int,  Project_ID int)
Create table Plastics ( Plastics_ID int,  Project_ID int)
Create table Glasses ( Glasses_ID int,  Project_ID int)
Create table Cups ( Cups_ID int,  Project_ID int)

insert into Project (Project_ID) values (1)
insert into Bottles (Bottle_ID,Project_ID) values (1,1)
insert into Bottles (Bottle_ID,Project_ID) values (2,1)
insert into Bottles (Bottle_ID,Project_ID) values (3,1)
insert into Plastics (Plastics_ID,Project_ID) values (1,1)
insert into Plastics (Plastics_ID,Project_ID) values (2,1)
insert into Glasses (Glasses_ID,Project_ID) values (1,1)
insert into Glasses (Glasses_ID,Project_ID) values (2,1)
insert into Cups (Cups_ID,Project_ID) values (1,1)
insert into Cups (Cups_ID,Project_ID) values (2,1)

select distinct p.Project_ID, 
(select COUNT(*) from Bottles where p.Project_ID=Project_ID) Bottles ,
(select COUNT(*) from Plastics where p.Project_ID=Project_ID) Plastics ,
(select COUNT(*) from Glasses where p.Project_ID=Project_ID) Glasses ,
(select COUNT(*) from Cups where p.Project_ID=Project_ID) Cups 
from Project p

Upvotes: 2

fez
fez

Reputation: 1835

Do you mean you wish you display the unique project ID's which have at least >=1 Bottle, >=1 Plastic , >=1 Glass, >=1 one Cup? If so you could write a query like this which joins all the tables based on the FK (ProjectID) and group by ProjectID:

SELECT p.ProjectID
    ,COUNT(DISTINCT (b.Bottle_ID)) 'CountBottles'
    ,COUNT(DISTINCT (p.Plastic_ID)) 'CountPlastic'
    ,COUNT(DISTINCT (g.Glass_ID)) 'CountGlass'
    ,COUNT(DISTINCT (c.Cup_ID)) 'CountCup'
FROM Project p
INNER JOIN Bottles b ON p.projectID = b.projectID
INNER JOIN Plastics pl ON pl.projectID = p.projectID
INNER JOIN Glasses g ON g.projectID = p.projectID
INNER JOIN Cups c ON c.projectID = p.projectID
GROUP BY p.ProjectID

Upvotes: 1

alybaba726
alybaba726

Reputation: 400

SELECT P.Project_ID, COUNT(B.BOTTLE_ID) AS BOTTLECOUNT,
COUNT(L.PLASTIC_ID) AS PLASTICCOUNT,
COUNT(G.GLASSES_ID) AS GLASSCOUNT,
COUNT(C.CUP_ID) AS CUPCOUNT
FROM PROJECT AS P
INNER JOIN BOTTLES B
ON P.PROJECT_ID=B.PROJECT_ID
INNER JOIN PLASTIC L
ON P.PROJECT_ID=L.PROJECT_ID
INNER JOIN GLASSES G
ON P.PROJECT_ID = G.PROJECT_ID
INNER JOIN CUPS C
ON P.PROJECT_ID=C.PROJECT_ID
GROUP BY P.PROJECT_ID, BOTTLE_ID, PLASTIC_ID, GLASSES_ID, CUP_ID
ORDER BY PROJECT_ID

Upvotes: 1

Menno
Menno

Reputation: 12641

select p.project_id, count(distinct bottle_id), count(distinct plastic_id), count(distinct glass_id), count(distinct cup_id)
from project p
left join bottles b on p.project_id = b.project_id
left join plastics pl on p.project_id = pl.project_id
left join glasses g on p.project_id = g.project_id
left join cups c on p.project_id = c.project_id
group by p.project_id

Upvotes: 3

Related Questions