Posted by David Portas on 11/14/06 01:27

k4 wrote:
> I have a database that contains a column for UnitName , BeginDate and
> EndDate.
>
> I want to pass two parameters (@BeginDate and @EndDate) and retrieve a
> table of values
>
> that include UnitName along with Counts for each UnitName.
>
> SELECT UnitName, COUNT(BeginDate) AS Start
> (SELECT COUNT(EndDate) AS Finish WHERE EndDate BETWEEN @BeginDate AND
> @EndDate)
> FROM Table
> WHERE BeginDate BETWEEN @BeginDate AND @EndDate
> GROUP BY UnitName
> ORDER BY UnitName
>
> This works. But when I try to add another count by using a subselect I
> get an error dealing with GROUP BY not including the column in my
> subselect.
>
> How is the best way to Count two columns using Group By.

Assuming the BeginDates are always <= the corresponding EndDates, you
can do:

SELECT UnitName,
COUNT(CASE WHEN BeginDate
BETWEEN @BeginDate AND @EndDate THEN 1 END) AS BeginDate,
COUNT(CASE WHEN EndDate
BETWEEN @BeginDate AND @EndDate THEN 1 END) AS EndDate
FROM tbl
WHERE BeginDate <= @EndDate
AND EndDate >= @BeginDate
GROUP BY UnitName
ORDER BY UnitName;

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

• Next in forum: urgent help!!!!!
• Prev in forum: COUNT FUNCTION ON MULTIPLE COLUMNS
• Thread view: Re: COUNT FUNCTION ON MULTIPLE COLUMNS

[Reply to this message]