CODEWITHSUNDEEP
sqlsql-servert-sqlselectgroup-by
Steve
Steve

Reputation: 93

Combining results before grouping in SQL

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

Upvotes: 5

Views: 107

Answers (3)

Saharsh Shah
Saharsh Shah

Reputation: 29051

Try this: 

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;

Upvotes: 0

Gabriele Petrioli
Gabriele Petrioli

Reputation: 195992

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

Upvotes: 4

StuartLC
StuartLC

Reputation: 107267

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;

Upvotes: 1

Related Questions