COUNT FUNCTION ON MULTIPLE COLUMNS

[k4]

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.

[David Portas]

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/m...S,SQL.90).aspx
--