如何在Server中按3列进行分组并获得计数？

Question

我正在尝试创建一些按三列分组的SQL，然后获取形成分组结果的行的COUNT。

而且，首先，由最高的数来排序。

我创建了一个SqlFiddle来帮我。

表模式(简化)：

CREATE TABLE [dbo].[Foo]
(
    [FooId] [int] IDENTITY(1,1) NOT NULL,
    [CreatedOn] [datetime] NOT NULL,
    [Company] [varchar](20) NOT NULL,
    [ProductFirstname] [varchar](100) NOT NULL,
    [ProductLastname] [varchar](100) NOT NULL
)

样本数据：

INSERT INTO Foo VALUES ('2001-10-01T07:07:07', 'Red', 'Yummy', 'GummyBear');
INSERT INTO Foo VALUES ('2002-10-01T07:07:07', 'Red', 'Yummy', 'GummyBear');
INSERT INTO Foo VALUES ('2003-10-01T07:07:07', 'Red', 'Bannana', 'Cake');
INSERT INTO Foo VALUES ('2003-11-11T07:07:07', 'Red', 'Green', 'Apples');
INSERT INTO Foo VALUES ('2004-10-01T07:07:07', 'Red', 'Yummy', 'GummyBear');
INSERT INTO Foo VALUES ('2005-10-01T07:07:07', 'Blue', 'Yummy', 'GummyBear');
INSERT INTO Foo VALUES ('2006-10-01T07:07:07', 'Blue', 'Yummy', 'GummyBear');
INSERT INTO Foo VALUES ('2007-10-01T07:07:07', 'Blue', 'Yummy', 'GummyBear');
INSERT INTO Foo VALUES ('2008-10-01T07:07:07', 'Red', 'Yummy', 'GummyBear');
INSERT INTO Foo VALUES ('2009-10-01T07:07:07', 'Blue', 'Green', 'Apples');

预期结果：

+-----------------------------------+
| Company | Name            | Count |
+-----------------------------------+
|    Red   | Yummy GummyBear | 4    |
|   Blue   | Yummy GummyBear | 3    |
|    Red   | Bannana Cake    | 1    |
|    Red   | Green Apples    | 1    |
|   Blue   | Green Apples    | 1    |
+-----------------------------------+

因此，对于这些数据，CreatedOn基本上被忽略了。这是另一种用途的元数据。

Answer 1

这就是你要找的：

SELECT 
    Company
    , ISNULL(ProductFirstname, '') + ' ' + ISNULL(ProductLastName, '') NAME
    , COUNT(*) Count
FROM foo
GROUP BY Company
    ,ISNULL(ProductFirstname, '') + ' ' + ISNULL(ProductLastName, '')
ORDER BY 3 DESC

这是一个工作的SQLFiddle

Answer 2

试着跟着，

Select company, name, count(*) as count from
(
select
  company,
  (ProductFirstname + ProductLastName) as name,
from foo
)T 
group by company, name
Order by 3 desc