SQL - 2 Counts in one query

I have 2 queries which return counts of different information in a table:

SELECT Date, COUNT(*) AS Total 
FROM Table
WHERE Type = 7 AND Date >= '2010-01-01' 
GROUP BY Date 
HAVING COUNT(*) > 5000  
ORDER BY Date

which returns the totals for all of the 'busy' dates:

Date       Total
---------- -----------
2010-01-05 9466
2010-02-02 8747
2010-03-02 9010
2010-04-06 7916
2010-05-05 9342
2010-06-02 8723
2010-07-02 7829
2010-08-03 8411
2010-09-02 7687
2010-10-04 7706
2010-11-02 8567
2010-12-02 7645

and

SELECT Date, COUNT(*) AS Failures
FROM Table
WHERE Type = 7 AND ErrorCode = -2 AND Date >= '2010-01-01' 
GROUP BY Date 
ORDER BY Date

which returns the total failures (all of which happened on busy dates):

Date       Failures
---------- -----------
2010-09-02 29
2010-10-04 16
2010-11-02 8

Is it possible to combine these into a single query to return one result?

E.g.:

Date       Total       Failures
---------- ----------- -----------
2010-01-05 9466
2010-02-02 8747
2010-03-02 9010
2010-04-06 7916
2010-05-05 9342
2010-06-02 8723
2010-07-02 7829
2010-08-03 8411
2010-09-02 7687         29
2010-10-04 7706         16
2010-11-02 8567         8
2010-12-02 7645

Answers


;With baseData As
(
SELECT 
      Date, 
      COUNT(*) AS Total, 
      COUNT(CASE WHEN ErrorCode = -2 THEN 1 END)  AS Failures
FROM Table
WHERE Type = 7 AND Date >= '2010-01-01' 
GROUP BY Date 
)
SELECT 
     Date,
     Total,
     Failures, 
     CAST(Failures AS float)/Total AS Ratio
FROM baseData
WHERE Total > 5000  OR Failures > 0
ORDER BY Date

Need Your Help

Search with two input fields in codeigniter

php mysql codeigniter search cookies

I have a searchform to search factories in my database. my factories table has a field called 'postcode' now when you visit the site for the first time you have to type in your postcode. this will be

What kind of handheld device/OS to use to interface with a Win Forms/ SQL Server Express App?

sql-server vb.net mobile handheld

I'm currently working on an VB.net application using Win forms and SQL Server Express 2008. One of the major functions of our program is to generate work orders for field engineers. These engineers

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.