将DB中的项目/行计为另一列分组的列 [英] Counting Items/Rows in DB as Columns Grouped by Another Column

问题描述

我想要做的基本上是：

 select Type, (Count(*) where Date='')as 10/1, (Count(*) where Date='')as 10/2
 from my table
 group by Type

我想要的样子是：

 Type   10/1    10/2
 1      5       7
 2      3       1
 3      6       9
 4      1       3
 5      9       8

但是，当我尝试在每个计数列中运行一个完整的选择，我会得到

However, when I try to run a full select within each count column, I end up getting

 Type   10/1    10/2
 1      12      15
 2      12      15
 3      12      15
 4      12      15
 5      12      15

任何建议。我不知道我是否需要运行一个枢轴或不，但我不会这样认为。此外，在我可以运行任何特定的一天，我正在想把日期放入一个变量，并试图运行一个日期范围的整个事情，动态生成列的每一天的运行。

Any suggestions are appreciated. I'm not sure if I will need to run a pivot or not, but I wouldn't think so. Additionally after I can run that for any specific day, I was thinking about trying to put the date into a variable and trying to run the whole thing for a date range, generating columns dynamically for each day its run. I would probably create a new question for that though.

推荐答案

试试这个;

SELECT TYPE
      ,SUM(CASE WHEN MyDate = '' THEN 1 ELSE 0 END) AS [10/1]
      ,SUM(CASE WHEN MyDate = '' THEN 1 ELSE 0 END) AS [10/2]
FROM MyTable
GROUP BY TYPE

这篇关于将DB中的项目/行计为另一列分组的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！