Count query problem - 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 query problem

Author

Topic

sbt1
Yak Posting Veteran

89 Posts

Posted - 2005-04-11 : 15:17:51

Trying to return several values.

This works:
Count(Tasks.TaskID) FROM Tasks INNER JOIN TaskDates ON Tasks.TaskID=TaskDates.TaskID
WHERE TaskDates.DateValue > '01/01/2005'
AND TaskDates.DateValue <'04/12/2005'
AND TaskDates.Status = 'Scheduled'

But this doesn't (trying to get multiple values):
SELECT
Count(Tasks.TaskID) FROM Tasks INNER JOIN TaskDates ON Tasks.TaskID=TaskDates.TaskID
WHERE TaskDates.DateValue > '01/01/2005'
AND TaskDates.DateValue <'03/31/2005'
AND TaskDates.Status = 'Complete'
AS Comp,
Count(Tasks.TaskID) FROM Tasks INNER JOIN TaskDates ON Tasks.TaskID=TaskDates.TaskID
WHERE TaskDates.DateValue > '01/01/2005'
AND TaskDates.DateValue <'03/31/2005'
AND TaskDates.Status = 'Complete'
AS Schd

Isn't there some way to do this?

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2005-04-11 : 15:31:22

SELECT
Count(Tasks.TaskID) AS Comp,

(SELECT Count(Tasks.TaskID) FROM Tasks INNER JOIN TaskDates ON Tasks.TaskID=TaskDates.TaskID
WHERE TaskDates.DateValue > '01/01/2005'
AND TaskDates.DateValue <'03/31/2005'
AND TaskDates.Status = 'Complete'
)
AS Schd

FROM Tasks INNER JOIN TaskDates ON Tasks.TaskID=TaskDates.TaskID
WHERE TaskDates.DateValue > '01/01/2005'
AND TaskDates.DateValue <'03/31/2005'
AND TaskDates.Status = 'Complete'

HTH

=================================================================
In order to improve the mind, we ought less to learn than to contemplate.
-Rene Descartes, philosopher and mathematician (1596-1650)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-04-11 : 15:34:14

Bustaz Kool

me but I'll post anyway...

Here are a couple ways: (if the dateRanges are the same for both status')


SELECT	TaskDates.Status
	,Count(Tasks.TaskID) taskCount
FROM	Tasks 
JOIN	TaskDates ON Tasks.TaskID=TaskDates.TaskID
WHERE	TaskDates.DateValue > '01/01/2005'
AND	TaskDates.DateValue <'03/31/2005' 
AND	TaskDates.Status IN ('Complete', 'Scheduled')
Group by TaskDates.Status


select	comp = sum(comp)
	,schd = sum(schd)
From	(
	SELECT	comp = case when status = 'Complete' then 1 else 0 end
		,schd = case when status = 'Scheduled' then 1 else 0 end
	FROM	Tasks 
	JOIN	TaskDates ON Tasks.TaskID=TaskDates.TaskID
	WHERE	TaskDates.DateValue > '01/01/2005'
	AND	TaskDates.DateValue <'03/31/2005' 
	AND	TaskDates.Status IN ('Complete', 'Scheduled')
	) a

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources