CODEWITHSUNDEEP
phpmysql
Navxxi
Navxxi

Reputation: 21

how to count number of Project

I have a database in mysql. The database contain three table such as "scheme","project","scheme_project. the main entities are scheme , project and i am using the scheme_project to make "Many to Many" relation between scheme and project.the following are tables field and data

------scheme----------------------
schemeid    scheme_name    
   1         DWSS
   2         Food and sec
   3         DRM
   4         VST
------------------------------------

another table

-------project----------------------
projectid    project_name
   1          WASH
   2          WHH
   3          SSS
------------------------------------

another table 

----------scheme_project------------

id      scheme_id        projectid

1           1               2
2           1               1
3           2               1
4           3               2
5           4               1
------------------------------------

So now i need a query to find the total number of project for all scheme. i have probleum when i count projectid it show me 5 but actually the projects are 2 in this case. anyone help please.

Upvotes: 0

Views: 203

Answers (3)

devpro
devpro

Reputation: 16117

You need to use GROUP BY like that:

SELECT COUNT(*) as Total,p.project_name, s.scheme_name FROM scheme s
INNER JOIN scheme_project ON sp.scheme_id = s.schemeid
INNER JOIN project p ON p.projectid = sp.projectid
GROUP BY p.project_name, s.scheme_name

This will return you how many projects listed in a scheme.

Upvotes: 0

Siddhartha esunuri
Siddhartha esunuri

Reputation: 1144

Use distinct

select count(distinct projectid) from yourtable

Upvotes: 1

AnkiiG
AnkiiG

Reputation: 3488

You can group by projectid as below

select scheme_id, count(*) as total_projects 
from scheme_project
group by projectid

Upvotes: 0

Related Questions