Combining results before grouping in SQL

Question

I want to work out the male/female split of my customer based on the person's title (Mr, Mrs, etc)

To do this I need to combine the result for the Miss/Mrs/Ms into a 'female' field.

The query below gets the totals per title but I think I need a sub query to return the combined female figure.

Any help would be greatly appreciated.

Query:
SELECT c.Title, COUNT(*) as Count
FROM
   Customers c
GROUP BY Title
ORDER By [Count] DESC

Answer:
Mr  1903
Miss    864
Mrs 488
Ms  108

Answer 1

You could do it like this

SELECT 
  [Gender] = CASE [Title] WHEN 'Mr' THEN 'M' ELSE 'F' END, 
  COUNT(*) as Count
FROM
   Customers c
GROUP BY 
  CASE [Title] WHEN 'Mr' THEN 'M' ELSE 'F' END
ORDER By 
  [Count] DESC

Demo at http://sqlfiddle.com/#!3/05c74/4

Answer 2

You can use CASE to project the new groups for the Titles:

SELECT SUM(CASE WHEN Title IN ('Mr') THEN 1 ELSE 0 END) AS Male,
       SUM(CASE WHEN Title IN ('Miss', 'Ms', 'Mrs') THEN 1 ELSE 0 END) AS Female
FROM
   Customers c;