Single-line Rowcounts

This is just another clever way to use the COUNT function, returning two distinct counts in the same single line result-set.  This example is a call center, I just want to get the number of successful calls, and disconnected calls, by day (call date)... in one query, not two:

  --working table variable to store your results
  DECLARE @counts TABLE (
  CallDate CHAR(10),SuccesfulCalls INT,DisconnectedCalls INT
  )
  INSERT @counts (
  CallDate,SuccesfulCalls,DisconnectedCalls
  )
  --collect both counts joined by calldate
  SELECT 
   a.[Call Date],a.[Successful Calls],b.[Disconnected Calls]
FROM
(
 SELECT 
CONVERT(VARCHAR(12),c.CallDate,101) [Call Date],
COUNT(1) [Successful Calls]
 FROM
dbo.Call_Table c
 WHERE
c.callstatus <> 'Disconnected'
 GROUP BY
CONVERT(VARCHAR(12),c.CallDate,101)
) a
INNER JOIN
(
 SELECT 
CONVERT(VARCHAR(12),c.CallDate,101) [Call Date],
COUNT(1) [Disconnected Calls]
 FROM
dbo.Call_Table c
 WHERE
c.callstatus = 'Disconnected'
 GROUP BY
CONVERT(VARCHAR(12),c.CallDate,101)
) b
ON a.[Call Date] = b.[Call Date]
ORDER BY a.[Call Date]


--select back out from your working table variable
SELECT 
CONVERT(CHAR(12),[CallDate],120) [Call Date],
SuccesfulCalls [Successful Calls],
DisconnectedCalls [Disconnected Calls]
FROM
@counts
ORDER BY 
CONVERT(DATETIME,[CallDate],101)


Example Results:
CallDate     SuccessfulCalls DisconnectedCalls 
 10/28/2010        125             2 
 11/4/2010         117             1 
 11/19/2010        181             1 
 11/26/2010        148             1 
 12/3/2010         105             2 
 12/8/2010         107             1 
 1/7/2011          194             1 
 1/17/2011         343             6 
 1/18/2011         219            31 
 1/19/2011         190            68 
 1/20/2011         183            83 
 1/21/2011         199           125 
 1/24/2011         230           155 
 1/25/2011         173           184 
 1/26/2011         104           203  


TIP:
You can't order by a date converted to string, if it is not in YYYYMMDD format.  That's why we have to do our ORDER BY like this:    CONVERT(DATETIME,[CallDate],101)