Count rows including zero. - 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 2000 Forums

SQL Server Development (2000)

Count rows including zero.

Author

Topic

jonhath
Starting Member

13 Posts

Posted - 2007-11-08 : 10:51:48

I'm trying to get a query to return all items in a column with a count of how many times they exist in a different table, including zero.


SELECT DISTINCT tblTraining_Times.Location_Date_Time, qryCountTrainingTimes.CountOfsubmissionkey, tblTraining_Times.Max_Number_In_Session

FROM qryCountTrainingTimes INNER JOIN tblTraining_Times 

ON 
qryCountTrainingTimes.Location_Date_Time = tblTraining_Times.Location_Date_Time;

This returns:


Location_Date_Time	   CountOfsubmissionkey	   Max_Number_In_Session
Austin - 1/2/3/4567 - 6:34 AM	   2	                 30
Davenport - 6/15/2007 - 2:00 PM	   1	                 50

When I really want it to return:


Location_Date_Time	     CountOfsubmissionkey	Max_Number_In_Session
Austin - 1/2/3/4567 - 6:34 AM	     2	                 30
Davenport - 6/15/2007 - 2:00 PM	     1	                 50
Boisie - 4/3/2/1922 - 6:30 AM	     0                   20

Is there any way to do this?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-08 : 11:02:55

Most probable change "INNER JOIN" to "LEFT JOIN".

E 12°55'05.25"
N 56°04'39.16"

jonhath
Starting Member

13 Posts

Posted - 2007-11-08 : 11:24:13

LEFT JOIN returns the same results.

SELECT DISTINCT tblTraining_Times.Location_Date_Time, qryCountTrainingTimes.CountOfsubmissionkey, tblTraining_Times.Max_Number_In_Session

FROM qryCountTrainingTimes LEFT JOIN tblTraining_Times 

ON qryCountTrainingTimes.Location_Date_Time = tblTraining_Times.Location_Date_Time;

Location_Date_Time	CountOfsubmissionkey	Max_Number_In_Session
Austin - 1/2/3/4567 - 6:34 AM	2	30
Davenport - 6/15/2007 - 2:00 PM	1	50

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-08 : 11:26:55

please provide the sample data for these 2 tables and the expected result

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-08 : 11:28:12

Now I see. Change INNER JOIN to RIGTH JOIN.

E 12°55'05.25"
N 56°04'39.16"

jonhath
Starting Member

13 Posts

Posted - 2007-11-08 : 11:32:03

I should also post this. This is "qryCountTrainingTimes"

SELECT tblTraining_Times.Location_Date_Time, Count(tblTraining.submissionkey) AS CountOfsubmissionkey

FROM tblTraining LEFT JOIN tblTraining_Times ON tblTraining.Location_Date_Time = tblTraining_Times.Location_Date_Time

GROUP BY tblTraining_Times.Location_Date_Time;

jonhath
Starting Member

13 Posts

Posted - 2007-11-08 : 11:33:05

quote:
Originally posted by Peso

Now I see. Change INNER JOIN to RIGTH JOIN.

E 12°55'05.25"
N 56°04'39.16"

PERFECT!

Thank you very much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources