Count question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Count question

Author

Topic

emailuser
Yak Posting Veteran

74 Posts

Posted - 2011-03-24 : 09:22:56

I have a tables that contains a list of defects(issues) , one of the fields is the projectid ( this is a number 1,2,3 etc ), the query below returns a count againest the project id , this works fine

SELECT COUNT(Issues.ProjectID) as Total1, ProjectID
FROM Issues
Group by ProjectID

I now need to show the project name instead of the projectid, there is a table called projects which has the id and associated name, i have the following code but the count is totally different and incorrect, am i missing something very obvious ????



SELECT COUNT(Issues.ProjectID) as Total1, Projects.Name
FROM Issues, Projects
where Issues.ProjectID=Projects.ID
Group by Projects.Name

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-03-24 : 09:43:17

Use a subquery:

Select
~~Projects~~B.Name,
Total1
From
(
SELECT COUNT(Issues.ProjectID) as Total1, ProjectID
FROM Issues
Group by ProjectID
) A
Inner Join Projects B
On A.ProjectId = B.id

Corey

snSQL on previous signature "...Oh and by the way Seventhnight, your signature is so wrong! On so many levels, morally, gramatically and there is a typo!"

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-24 : 09:43:51

If any of the following queries return any rows, there are multiple rows in the Projects table that join to a single project id in the Issues table. That would be one reason you see this behavior.

select ProjectId,Name from Projects group by ProjectId,Name having count(*) > 1
select ProjectId from Projects group by ProjectId having count(*) > 1
select Name from Projects group by Name having count(*) > 1

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-24 : 09:45:03

Seventhnight you beat me by 34 seconds!!!! :--)

emailuser
Yak Posting Veteran

74 Posts

Posted - 2011-03-24 : 10:24:20

Thanks everyone for a quick reply
i did a



select ProjectId,Name from Projects group by ProjectId,Name having count(*) > 1
select ProjectId from Projects group by ProjectId having count(*) > 1
select Name from Projects group by Name having count(*) > 1

the last select statement returned 1 entry

i then did



Select 
Projects.Name,
Total1
From 
(
SELECT COUNT(Issues.ProjectID) as Total1, ProjectID
FROM Issues
Group by ProjectID
) A
Inner Join Projects B
On A.ProjectID = B.ID

but received error



line2 the multipart identifier Projects.Name could not be bound

bit of a newbie on subqueries ... any more help appreciated

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-24 : 10:57:43

quote:
Originally posted by emailuser

Thanks everyone for a quick reply
i did a

select ProjectId,Name from Projects group by ProjectId,Name having count(*) > 1
select ProjectId from Projects group by ProjectId having count(*) > 1
select Name from Projects group by Name having count(*) > 1

the last select statement returned 1 entry

i then did

Select
~~Projects~~ B.Name,
Total1
From
(
SELECT COUNT(Issues.ProjectID) as Total1, ProjectID
FROM Issues
Group by ProjectID
) A
Inner Join Projects B
On A.ProjectID = B.ID

but received error

line2 the multipart identifier Projects.Name could not be bound

bit of a newbie on subqueries ... any more help appreciated

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-03-24 : 13:25:05

whoops.

Corey

snSQL on previous signature "...Oh and by the way Seventhnight, your signature is so wrong! On so many levels, morally, gramatically and there is a typo!"

emailuser
Yak Posting Veteran

74 Posts

Posted - 2011-03-24 : 13:28:35

:) all working many thanks really appreciate the help and the very quick responses ...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources