Find count occurrences

Question

I have a table with 2 columns (db: sql server 2008):

id         name
-----      ------
1          Bob
2          Mike
3          Mary
4          Mike
5          Barry
6          Benson
7          Burrows

I want to get a count of names that start with B and start with M (in one row)?

Like:

Count of B        Count of M
-----------      ------------
4                  3

The only thing that comes up for me is a union. Any ideas to do it cleaner in a single query (no union)?

Answer 1

Try it by using CASE,

SELECT  SUM(CASE WHEN SUBSTRING(name,1,1) = 'B' Then 1 ELSE 0 END),
        SUM(CASE WHEN SUBSTRING(name,1,1) = 'M' Then 1 ELSE 0 END)
FROM TAbleName

SQLFiddle Demo

Answer 2

You can use PIVOT for this. If you have a known number of columns, then you can hard-code the values with a STATIC PIVOT:

select *
from
(
  select substring(name, 1, 1) name, -- use the same field twice, 
    substring(name, 1, 1) initial    -- once will be for the count the other for columns
  from yourtable
) x
pivot
(
  count(name)
  for initial in ([B], [M])
) p

See SQL Fiddle With Demo

If you have an unknown number of columns to transform, then you can use dynamic sql and create a Dynamic PIVOT:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' 
                      + QUOTENAME(substring(name, 1, 1)) 
                    from yourtable
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT ' + @cols + ' from 
             (
              select substring(name, 1, 1) name, 
                substring(name, 1, 1) initial
              from yourtable
            ) x
            pivot 
            (
                count(name)
                for initial in (' + @cols + ')
            ) p '

execute(@query)

See SQL Fiddle with Demo

If you then want to filter the data set down to only those that begin with B or M then you can use a WHERE clause to filter.

where substring(name, 1, 1) in ('B', 'M')

Answer 3

Here is another way

Declare @T Table ([id] varchar(5), [name] varchar(7));

INSERT INTO @T([id], [name])
VALUES
    ('1', 'Bob'),
    ('2', 'Mike'),
    ('3', 'Mary'),
    ('4', 'Mike'),
    ('5', 'Barry'),
    ('6', 'Benson'),
    ('7', 'Burrows')

;WITH CTE AS
(SELECT
    Initials = SUBSTRING(name,1,1)
    ,Cnt = COUNT([name]) 
FROM @t 
GROUP BY SUBSTRING(name,1,1))

SELECT 
    [Count of B] = (SELECT Cnt FROM CTE WHERE Initials = 'B')
    ,[Count of M] = (SELECT Cnt FROM CTE WHERE Initials = 'M')

Result

Count of B  Count of M
4             3