SQL - 2在一个查询中计数 [英] SQL - 2 Counts in one query

问题描述

我有2个查询，返回表中不同信息的计数：

  SELECT日期，COUNT 
 FROM Table 
 WHERE Type = 7 AND Date> ='2010-01-01'
 GROUP BY日期
 HAVING COUNT（*）> 5000 
 ORDER BY日期
  

返回所有忙碌日期的总计：

 日期总计
 ---------- ---------- -  
 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 
  

和

  SELECT Date，COUNT（*）AS失败
 FROM Table 
 WHERE Type = 7 AND ErrorCode = -2 AND Date> ='2010-01-01'
 GROUP BY日期
 ORDER BY日期
  

返回总失败次数（所有失败都发生在忙碌日期）：

 日期失败
 ---------- ----------- 
 2010-09-02 29 
 2010-10-04 16 
 2010- 11-02 8 
  

是否可以将这些结合到单个查询中以返回一个结果？ p>

例如：

 日期失败总数
 ------ ----------- ----------- 
 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 
  

解决方案

 使用baseData作为
（
 SELECT 
日期，
 COUNT（*）AS总计，
 COUNT（CASE WHEN错误代码= -2 THEN 1结束）AS失败
 FROM Table 
 WHERE Type = 7 AND Date> ='2010-01-01'
 GROUP BY日期
）
 SELECT 
日期，
总计，
失败，
 CAST（失败AS浮动）/总AS比率
 FROM baseData 
 WHERE Total> 5000 OR Failure> 0 
 ORDER BY日期
  

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

解决方案

;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

这篇关于SQL - 2在一个查询中计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！