Counts - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Counts

Author

Topic

Steve2106
Posting Yak Master

183 Posts

Posted - 2011-10-26 : 11:45:01

Hi There,

Need some help, Again!

I have a select statement that has a count in it.
All the records are selected from my database and the count gets either a 0 if there are no records or the count of records.
This has been working fine but I now need to add another count.
When I try to add the new count it only shows records where there are records in both of the tables being counted.
I need the new count to work the same as the first count I.E. showing all records with either a 0 for the count or the count of the records.
This is the bottom of my select statement:
tbMatTrack.LocationId,
tbMatLocation.Location,
tbMatLocation.Abbriviation,
Count(tbMatTrackSubActions.ActionId) As NumSubActions
From
tbMatTrack Inner Join
tbMatOrg On tbMatTrack.ResponsibleId = tbMatOrg.PersonnelId Inner Join
tbMatOrg tbMatOrg1 On tbMatTrack.ActioneeId = tbMatOrg1.PersonnelId Inner Join
tbMatTypes On tbMatTrack.TypeId = tbMatTypes.TypeId Inner Join
tbMatRisk On tbMatTrack.Risk = tbMatRisk.RiskId Inner Join
tbMatSource On tbMatTrack.SourceId = tbMatSource.SourceId Inner Join
tbMatLocation On tbMatTrack.LocationId = tbMatLocation.LocationId Left Join
tbMatTrackSubActions On tbMatTrack.ActionId = tbMatTrackSubActions.ActionId

I hope that makes sense and thanks for the help.

Best Regards,

Steve

Sachin.Nand

2937 Posts

Posted - 2011-10-26 : 12:16:19

Please post the whole query.

PBUH

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-26 : 12:36:10

seems like you need to convert one of inner join to left join.inner join will cause only rows with matches to be returned?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Steve2106
Posting Yak Master

183 Posts

Posted - 2011-10-26 : 12:38:52

Hi Sachin,
Thanks for the reply.

Whole Query:
SELECT
tbMatTrack.ActionId,
tbMatTrack.Description,
tbMatTrack.ResponsibleId,
tbMatOrg.LastName + ', ' + tbMatOrg.FirstName As RPFullName,
tbMatTrack.ActioneeId,
tbMatOrg1.LastName + ', ' + tbMatOrg1.FirstName As ActioneeFullName,
tbMatTrack.TypeId,
tbMatTypes.TypeDescription,
tbMatTrack.Risk,
tbMatRisk.Description As RiskDescription,
tbMatTrack.DateRaised,
tbMatTrack.PlannedStartDate,
tbMatTrack.ActualStartDate,
tbMatTrack.PlannedCompletionDate,
tbMatTrack.ActualCompletionDate,
tbMatTrack.EditorComments,
tbMatTrack.EditorActions,
tbMatTrack.SourceId,
tbMatSource.SourceDescription,
tbMatTrack.PerComplete,
tbMatTrack.LocationId,
tbMatLocation.Location,
tbMatLocation.Abbriviation,
Count(tbMatImages.ActionId) As NumDocs,
Count(tbMatTrackSubActions.ActionId) As NumSubActions
From
tbMatTrack Inner Join
tbMatOrg On tbMatTrack.ResponsibleId = tbMatOrg.PersonnelId Inner Join
tbMatOrg tbMatOrg1 On tbMatTrack.ActioneeId = tbMatOrg1.PersonnelId Inner Join
tbMatTypes On tbMatTrack.TypeId = tbMatTypes.TypeId Inner Join
tbMatRisk On tbMatTrack.Risk = tbMatRisk.RiskId Inner Join
tbMatSource On tbMatTrack.SourceId = tbMatSource.SourceId Inner Join
tbMatLocation On tbMatTrack.LocationId = tbMatLocation.LocationId LEFT JOIN
tbMatImages ON tbMatTrack.ActionId=tbMatImages.ActionId LEFT JOIN
tbMatTrackSubActions ON tbMatTrack.ActionId=tbMatTrackSubActions.ActionId
Group By tbMatTrack.ActionId,
tbMatTrack.Description,
tbMatTrack.ResponsibleId,
tbMatOrg.FirstName,
tbMatOrg.LastName,
tbMatTrack.ActioneeId,
tbMatOrg1.FirstName,
tbMatOrg1.LastName,
tbMatTrack.TypeId,
tbMatTypes.TypeDescription,
tbMatTrack.Risk,
tbMatRisk.Description,
tbMatTrack.DateRaised,
tbMatTrack.PlannedStartDate,
tbMatTrack.ActualStartDate,
tbMatTrack.PlannedCompletionDate,
tbMatTrack.ActualCompletionDate,
tbMatTrack.EditorComments,
tbMatTrack.EditorActions,
tbMatTrack.SourceId,
tbMatSource.SourceDescription,
tbMatTrack.PerComplete,
tbMatTrack.LocationId,
tbMatLocation.Location,
tbMatLocation.Abbriviation

Steve

Steve2106
Posting Yak Master

183 Posts

Posted - 2011-10-26 : 12:51:07

Hi Visakhm,
I tried that,
Still did not work.
There are a bout 35 rows that should come back but only 4 return with values the same in both count fields.

Thanks for your help, as always it's appreciated.

Best Regards,

Steve

X002548
Not Just a Number

15586 Posts

Posted - 2011-10-26 : 13:37:19

oh, crap, I went blind

Please format your code and wrap it in [ code] [ /code] tags (without the space

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Sachin.Nand

2937 Posts

Posted - 2011-10-26 : 14:55:50

Don't worry I ain't getting blind..

What does this return ?

Select Count(tbMatImages.ActionId) As NumDocs, Count(tbMatTrackSubActions.ActionId) As NumSubActions from tbMatTrack  
inner join tbMatImages ON tbMatTrack.ActionId=tbMatImages.ActionId LEFT JOIN
tbMatTrackSubActions ON tbMatTrack.ActionId=tbMatTrackSubActions.ActionId

PBUH

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-27 : 02:55:57

unless OP gives some sample data to show how data is present in tables, we can keep on guessing why count values are returning what OP expects

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Steve2106
Posting Yak Master

183 Posts

Posted - 2011-10-27 : 04:00:04

Hi,

I agree it is hard if there is no data but it's really hard to show sample data on this forum. It does not allow for images that could show a screenshot of the returned data.

I have now used a LEFT JOIN on both counts.
I have managed to drop a image link here:
[url]www.dotnet4site.com/ShareImages/SqlTeam27102011.jpg[/url]

Thanks for your help.

Best Regards,

Steve

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-27 : 04:13:54

quote:
Originally posted by Steve2106

Hi,

I agree it is hard if there is no data but it's really hard to show sample data on this forum. It does not allow for images that could show a screenshot of the returned data.

Thanks for your help.

Best Regards,

Steve

export data to excel and then copy and paste data along with column names from excel to here so that it will be properly formatted

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources