How to group by 3 columns -and- get a count in SQL Server? -

i'm trying create sql groups 3 columns , gets count of rows form grouped result.

also, ordered highest count, first.

i've created sqlfiddle me.

table schema (simplified):

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 ) 

sample data:

insert foo values ('2001-10-01t07:07:07', 'red', 'yummy', 'gummybear'); insert foo values ('2002-10-01t07:07:07', 'red', 'yummy', 'gummybear'); insert foo values ('2003-10-01t07:07:07', 'red', 'bannana', 'cake'); insert foo values ('2003-11-11t07:07:07', 'red', 'green', 'apples'); insert foo values ('2004-10-01t07:07:07', 'red', 'yummy', 'gummybear'); insert foo values ('2005-10-01t07:07:07', 'blue', 'yummy', 'gummybear'); insert foo values ('2006-10-01t07:07:07', 'blue', 'yummy', 'gummybear'); insert foo values ('2007-10-01t07:07:07', 'blue', 'yummy', 'gummybear'); insert foo values ('2008-10-01t07:07:07', 'red', 'yummy', 'gummybear'); insert foo values ('2009-10-01t07:07:07', 'blue', 'green', 'apples'); 

expected result:

+-----------------------------------+ | company | name            | count | +-----------------------------------+ |    red   | yummy gummybear | 4    | |   blue   | yummy gummybear | 3    | |    red   | bannana cake    | 1    | |    red   | green apples    | 1    | |   blue   | green apples    | 1    | +-----------------------------------+ 

so data, createdon ignored. it's meta data use.

this you're looking for:

select      company     , isnull(productfirstname, '') + ' ' + isnull(productlastname, '') name     , count(*) count foo group company     ,isnull(productfirstname, '') + ' ' + isnull(productlastname, '') order 3 desc 

here working sqlfiddle