用于检索标记/总行数的 TSQL 查询

发布于 2025-01-05 01:30:54 字数 383 浏览 4 评论 0原文

我正在努力寻找以下问题的解决方案。假设，有一个像这样的表，

 COL1 FLAG 
 aaa  1
 aaa  0
 aaa  1
 bbb  0

我需要编写一个查询来获取以下输出：

COL1_VALUE FLAGGED TOTAL
aaa        2       3
bbb        0       1

其中 FLAGGED 列包含 FLAG=1 的“aaa”行值的总数，TOTAL 列是包含以下内容的行总数： 'aaa'，换句话说，查找包含 'aaa' 的行被标记的数量与包含 'aaa' 的行总数的关系。是否可以通过单个查询来实现？（即不使用临时表等）

（MSSQL2008）

原文

I'm struggling to find a solution to the following problem.
Assume, one has a table like this

 COL1 FLAG 
 aaa  1
 aaa  0
 aaa  1
 bbb  0

I need to write a query to get the following output:

COL1_VALUE FLAGGED TOTAL
aaa        2       3
bbb        0       1

where FLAGGED column contains the total count of the 'aaa' row values for which FLAG=1, and TOTAL column is the total number of rows containing 'aaa', in other words find how many rows containing 'aaa' are flagged in relation to total number of rows containing 'aaa'. Is it possible with a single query? (i.e. without using temp tables etc.)

(MSSQL2008)

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

披肩女神 2025-01-12 01:30:54

SELECT COL1 AS COL1_VALUE,
       COUNT(CASE WHEN FLAG = 1 THEN 1 END) AS FLAGGED,
       COUNT(*) AS TOTAL
FROM YourTable
GROUP BY COL1

SELECT COL1 AS COL1_VALUE,
       COUNT(CASE WHEN FLAG = 1 THEN 1 END) AS FLAGGED,
       COUNT(*) AS TOTAL
FROM YourTable
GROUP BY COL1

回复收藏 0 原文

甜｀诱少女 2025-01-12 01:30:54

SELECT COL1, SUM(FLAG) AS FLAGGED, Count(*) AS TOTAL from tbl GROUP BY COL1

SELECT COL1, SUM(FLAG) AS FLAGGED, Count(*) AS TOTAL from tbl GROUP BY COL1

回复收藏 0 原文

浮萍、无处依 2025-01-12 01:30:54

SELECT    Tab.COL1 AS COL1_VALUE,
          SUM(CASE WHEN Tab.FLAG = 1 THEN 1 ELSE 0 END) AS FLAGGED,
          COUNT(*) AS TOTAL
FROM      Tab
GROUP BY  Tab.COL1

SELECT    Tab.COL1 AS COL1_VALUE,
          SUM(CASE WHEN Tab.FLAG = 1 THEN 1 ELSE 0 END) AS FLAGGED,
          COUNT(*) AS TOTAL
FROM      Tab
GROUP BY  Tab.COL1

回复收藏 0 原文

~没有更多了~