SQL: Two count functions in a single query?

Question

How do I get these two count functions into one query?

SELECT 
    COUNT(MaritalStatus) as 'Married'
FROM 
    Person.Person PP 
INNER JOIN 
    HumanResources.Employee HRE ON PP.BusinessEntityID = HRE.BusinessEntityID
WHERE 
    MaritalStatus = 'M';

SELECT 
    COUNT(MaritalStatus) as 'Single'
FROM 
    Person.Person PP 
INNER JOIN 
    HumanResources.Employee HRE ON PP.BusinessEntityID = HRE.BusinessEntityID
WHERE 
    MaritalStatus = 'S';

The tricks is here to use conditional aggregation by implementing COUNT through SUM. SUM(CASE WHEN a=b THEN 1 ELSE 0 END) — PM 77-1
– PM 77-1, Commented Dec 2, 2016 at 0:06
Your column aliases are non-standard SQL. Which DBMS are you using? In standard SQL you can use count(*) filter (where MaritalStatus = 'S')` — user330315
– user330315, Commented Dec 2, 2016 at 6:41

K. Li · Accepted Answer · 2016-12-02 00:09:06Z

1

You could try using sum case when:

SELECT SUM(CASE WHEN MaritalStatus = 'M' THEN 1 ELSE 0 END), 
SUM(CASE WHEN MaritalStatus = 'S' THEN 1 ELSE 0 END) 
FROM Person.Person PP INNER JOIN HumanResources.Employee HRE
ON PP.BusinessEntityID = HRE.BusinessEntityID

answered Dec 2, 2016 at 0:09

K. Li

681 silver badge12 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Res260 - Émilio Gonzalez · Accepted Answer · 2016-12-02 00:20:10Z

1

For this kind of request, you need to use aggregation functions.

SELECT MaritalStatus, COUNT(MaritalStatus)
FROM Person.Person PP
    INNER JOIN HumanResources.Employee HRE
        ON PP.BusinessEntityID = HRE.BusinessEntityID
GROUP BY MaritalStatus;

This will output something like this:

MaritalStatus  | COUNT(MaritalStatus)
               |
M              | 50
S              | 20

As two rows. You can add ORDER BY MaritalStatus to ensure that you receive the data in the good order, because the order by which you receive the data is not deterministic.

The advantage of using this over SUM() is that if you ever add another marital status, your request would not change.

answered Dec 2, 2016 at 0:20

Res260 - Émilio Gonzalez

113 bronze badges

1 Comment

user1451111 Over a year ago

one point from me for mentioning the advantage over the SUM() technique.

Keith John Hutchison · Accepted Answer · 2016-12-02 00:39:25Z

1

This also works and is easy enough to extend to other MaritalStatuses.

SELECT married.*, single.* from
(   SELECT COUNT(MaritalStatus) as 'Married'
    FROM Person.Person PP INNER JOIN HumanResources.Employee HRE
    ON PP.BusinessEntityID = HRE.BusinessEntityID
    WHERE MaritalStatus = 'M'
    ) married
, (
    SELECT COUNT(MaritalStatus) as 'Single'
    FROM Person.Person PP INNER JOIN HumanResources.Employee HRE
    ON PP.BusinessEntityID = HRE.BusinessEntityID
    WHERE MaritalStatus = 'S'
    ) single

answered Dec 2, 2016 at 0:39

Keith John Hutchison

5,34711 gold badges49 silver badges66 bronze badges

Comments

aaronsteers · Accepted Answer · 2016-12-02 00:46:24Z

1

Since your from and join clauses are identical, simply move the where conditions into a case statement.

SELECT COUNT(case when MaritalStatus = 'M' then 1 end) as 'Married',
       COUNT(case when MaritalStatus = 'S' then 1 end) as 'Single'
FROM Person.Person PP 
JOIN HumanResources.Employee HRE
  ON PP.BusinessEntityID = HRE.BusinessEntityID

answered Dec 2, 2016 at 0:46

aaronsteers

2,6403 gold badges26 silver badges44 bronze badges

Comments

xhienne · Accepted Answer · 2016-12-02 00:17:59Z

0

With MySQL this should work:

SELECT SUM(MaritalStatus = 'M') as 'Married',
       SUM(MaritalStatus = 'S') as 'Single'
FROM Person.Person PP INNER JOIN HumanResources.Employee HRE
ON PP.BusinessEntityID = HRE.BusinessEntityID
WHERE MaritalStatus IN ('M', 'S');

("MaritalStatus = 'X') is a boolean that evaluates to 0 (false) or 1 (true). On other servers than MySQL, a cast may be necessary.

answered Dec 2, 2016 at 0:17

xhienne

6,2141 gold badge19 silver badges36 bronze badges

Comments

Jeff · Accepted Answer · 2016-12-02 00:31:35Z

0

You can use group and have both:

SELECT COUNT(MaritalStatus) as 'Status'
    FROM Person.Person PP INNER JOIN HumanResources.Employee HRE
    ON PP.BusinessEntityID = HRE.BusinessEntityID
  group by MaritalStatus;

or you can use decode

edited Dec 2, 2016 at 0:31

Jeff

12.9k5 gold badges36 silver badges63 bronze badges

answered Dec 2, 2016 at 0:09

Jannes

11 bronze badge

2 Comments

user1451111 Over a year ago

Note #1: 'decode' operator is available in Oracle RDMS only.

user1451111 Over a year ago

Note #2: A note for anyone who stumble upon this answer in future. For OP's situation (getting sub-counts from the table) this answer will work great but it may cause issue when the intention is to get the total row count of the table also as an additional column and some of the 'MaritalStatus' columns are NULL. In that case the sum of all the sub-counts will not be equal to the total row count.

Collectives™ on Stack Overflow

SQL: Two count functions in a single query?

6 Answers 6

Comments

1 Comment

Comments

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

1 Comment

Comments

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related