Combining results before grouping in SQL

Question

I want to work out a split between the men and women of my client based on the name of the person (Mr., Mrs., etc.).

To do this, I need to combine the result for Miss / Mrs / Ms in the "woman" field.

The following query gets the totals for each header, but I think I need an extra query to return the merged shape.

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 can 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 create new groups for headings:

 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; 

Answer 3

Try the following:

 SELECT (CASE WHEN c.Title = 'Mr' THEN 'Male' WHEN c.Title IN ('Mrs', 'Miss', 'Ms') THEN 'Female' ELSE 'NA' END) AS title, COUNT(1) AS PeopleCount FROM Customers c GROUP BY (CASE WHEN c.Title = 'Mr' THEN 'Male' WHEN c.Title IN ('Mrs', 'Miss', 'Ms') THEN 'Female' ELSE 'NA' END) ORDER By PeopleCount DESC;