Ok, I'm a moron.  SQL Server Books Online, "GROUP BY ALL", problem 
solved.  Working query listed below.  I like this format for sending the 
query to the list too.

SELECT    
	Projects.Project_Key,
	Projects.Project_Name,
	COUNT(Projects.Project_Key) AS MyCount,
	Project_Type.Project_Type_Name
FROM        
	Projects
LEFT OUTER JOIN
	Master ON Projects.Project_Key =3D Master.Project_Key
LEFT OUTER JOIN
	Project_Type ON Projects.Project_Type_Key =3D 
Project_Type.Project_Type_Key
WHERE    
	(Master.Original_Date > '01/01/2002')
AND
	(Master.Original_Date < '08/01/2002')
GROUP BY ALL
	Projects.Project_Key,
	Projects.Project_Name,
	Projects.Project_Type_Key,
	Project_Type.Project_Type_Name
ORDER BY
	Projects.Project_Type_Key,
	Projects.Project_Name

Jerry

-----Original Message-----
From: Jerry Diegel
Sent: Thursday, July 11, 2002 8:40 AM
To: sql language
Subject: [sql_language] Re: Lack of 0 counts


Ken,

Thanks for the tip.  It has never occured to me that I could put the 
criteria in the Join like this.  However, I received the same results 
with your logic as I did with mine.  I think the problem is with the 
grouping.  I say that after running the following to querys.

-- QUERY 1
SELECT     COUNT(Project_Key) AS MyCount
FROM         Master
WHERE     (Project_Key =3D 4028)
; -- QUERY 2
SELECT     Project_Key, COUNT(Project_Key) AS MyCount
FROM         Master
WHERE     (Project_Key =3D 4028)
Group By Project_Key

QUERY 1 returns 0, while QUERY 2 returns nothing.  Personnally, I don't 
understand why QUERY 2 returns nothing.  I guess I need to bone up on 
that aspect of SQL.  I do think that once I figure that out I can fix my 
query from the original post.  On the same note, the original code that 
was used to get these results used an array of Project_Keys and ran a 
query like QUERY 1 for each key, which was a couple 1000 queries running 
for one page (ASP).  I was trying to combine that logic into one query.  
I'm getting the same results as before for Project_Key's that have a 
MyCount > 0, but those with a MyCount =3D 0 are not showing up when I 
use the Group By logic.

Again, thanks for your help.

Jerry

-----Original Message-----
From: Ken Schaefer [mailto:ken@a...]
Sent: Thursday, July 11, 2002 12:13 AM
To: sql language
Subject: [sql_language] Re: Lack of 0 counts


Because you didn't provide any data, I'm only guessing at what the 
problem
is:

a) It may be a problem with dates - try using ISO date format 
(yyyy/mm/dd)
just in case SQL Server is miscontruing your date format

b) Possibly the WHERE clause is restricting your result set - so you 
aren't
getting the results you think. There are probably no 0 counts once the
Master clauses are applied. Try the following (notice that I've moved 
the
criteria into the JOIN clause, and out of the WHERE clause)

SELECT
    Projects.Project_Key,
    Projects.Project_Name,
    COUNT(Projects.Project_Key) AS MyCount,
    Project_Type.Project_Type_Name
FROM
    Projects
LEFT OUTER JOIN
    Master
ON
    Projects.Project_Key =3D Master.Project_Key
LEFT OUTER JOIN
    Project_Type
ON
    Projects.Project_Type_Key =3D Project_Type.Project_Type_Key
AND
    Master.Original_Date > '2002/01/01'     -- use ISO dates here
AND
    Master.Original_Date < '2002/08/01'
GROUP BY
    Projects.Project_Key,
    Projects.Project_Name,
    Projects.Project_Type_Key,
    Project_Type.Project_Type_Name
ORDER BY
    Projects.Project_Type_Key,
    Projects.Project_Name

Cheers
Ken


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Jerry Diegel" <diegelj@g...>
Subject: [sql_language] Lack of 0 counts


Here's my query:

SELECT     Projects.Project_Key, Projects.Project_Name,
COUNT(Projects.Project_Key) AS MyCount, Project_Type.Project_Type_Name
FROM         Projects LEFT OUTER JOIN
                      Master ON Projects.Project_Key =3D 
Master.Project_Key
LEFT OUTER JOIN
                      Project_Type ON Projects.Project_Type_Key =3D
Project_Type.Project_Type_Key
WHERE     (Master.Original_Date > '01/01/2002') AND 
(Master.Original_Date <
'08/01/2002')
GROUP BY Projects.Project_Key, Projects.Project_Name,
Projects.Project_Type_Key, Project_Type.Project_Type_Name
ORDER BY Projects.Project_Type_Key, Projects.Project_Name

Can anyone tell me why I'm not get any results where MyCount =3D 0 and 
what I
can change to accomplish this.  I know there should be a whole slew of 
0's
in there.

Thanks,

Jerry