SEO

#1 (**permalink**) 03-01-2008, 11:34 AM

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.

#2 (**permalink**) 03-01-2008, 11:34 AM

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
--

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

COUNT FUNCTION ON MULTIPLE COLUMNS