MySQL: How to count from columns separately?

Question

Usually I feel pretty confident with SQL queries, however this one has me scratching my head. I feel like this -should- be a quick fix, but I'm just not seeing it.

I'm trying to do a count on multiple values on the same table, in one query.

Don't mind the "0000000000000000" it's just representing an empty byte array.

Is there an easy way to combine these queries?

SELECT COUNT(ssn)
FROM patients
WHERE ssn="0000000000000000";

SELECT COUNT(firstname)
FROM patients
WHERE firstname="0000000000000000"

SELECT COUNT(lastname)
FROM patients
WHERE lastname="0000000000000000"

etc...

Joe Stefanelli · Accepted Answer · 2011-09-01 20:10:04Z

6

SELECT SUM(CASE WHEN ssn = '0000000000000000' THEN 1 ELSE 0 END) AS ssn_count,
       SUM(CASE WHEN firstname = '0000000000000000' THEN 1 ELSE 0 END) AS first_count,
       SUM(CASE WHEN lastname = '0000000000000000' THEN 1 ELSE 0 END) AS last_count
    FROM patients
    WHERE ssn = '0000000000000000'
        OR firstname = '0000000000000000'
        OR lastname = '0000000000000000'

edited Sep 1, 2011 at 20:10

answered Sep 1, 2011 at 19:49

Joe Stefanelli

136k21 gold badges243 silver badges241 bronze badges

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

5 Comments

Marvo Over a year ago

That's pretty darned cool. How do you think it compares in performance to the "union" solutions below?

Joe Stefanelli Over a year ago

@Marvo: My standard answer is try both and compare. I'd assume this would be better as it's 1 select vs. 3.

James Over a year ago

I guess it depends on how it gets executed; e.g. it might be one full table scan, rather than three index range scans, which could be very different, depending on the table contents.

Marvo Over a year ago

Thanks, @Joe. I wonder if you'd be guaranteed a table scan with this solution, or if it could make effective use of indexes. So yeah, performance might be situation dependent. Still, very cool.

Joe Stefanelli Over a year ago

@Marvo: A WHERE clause might help avoid the table scan. I'll add one to the answer.

Sam Dufel · Accepted Answer · 2011-09-01 19:49:46Z

2

You can do something like this -

SELECT COUNT(ssn) AS patient_count, 'ssn' AS count_type
FROM patients
WHERE ssn="0000000000000000";
UNION
SELECT COUNT(firstname) AS patient_count, 'firstname' AS count_type
FROM patients
WHERE firstname="0000000000000000"
UNION
SELECT COUNT(lastname) AS patient_count, 'lastname' AS count_type
FROM patients
WHERE lastname="0000000000000000"

answered Sep 1, 2011 at 19:49

Sam Dufel

17.6k3 gold badges51 silver badges52 bronze badges

2 Comments

theangryhornet Over a year ago

Is there a way I can then pivot the table? I mean it would be nicer to have my tiles as headers, not values.

James Over a year ago

You could put UNION ALL instead of UNION, just to save the SQL server a bit of redundant work.

Vitor Furlanetti · Accepted Answer · 2011-09-01 19:49:07Z

1

Try with UNION

SELECT COUNT(ssn)
FROM patients
WHERE ssn="0000000000000000";
UNION
SELECT COUNT(firstname)
FROM patients
WHERE firstname="0000000000000000"
UNION    
SELECT COUNT(lastname)
FROM patients
WHERE lastname="0000000000000000"

answered Sep 1, 2011 at 19:49

Vitor Furlanetti

4513 silver badges8 bronze badges

3 Comments

Joe Stefanelli Over a year ago

You'd want to use UNION ALL here. If two (or even all three) counts happen to be the same, UNION would eliminate them as duplicates.

theangryhornet Over a year ago

Not sure if I like this one, as you can't distinguish which counts are which.

Vitor Furlanetti Over a year ago

you could put another column with the description to solve this

James · Accepted Answer · 2011-09-01 20:08:22Z

1

I guess this would work?

SELECT *
FROM
(SELECT COUNT(ssn) AS ssn_count
 FROM patients
 WHERE ssn="0000000000000000") AS ssn
CROSS JOIN
(SELECT COUNT(firstname) AS firstname_count
 FROM patients
 WHERE firstname="0000000000000000") AS firstname
CROSS JOIN
(SELECT COUNT(lastname) AS lastname_count
 FROM patients
 WHERE lastname="0000000000000000") AS lastname

answered Sep 1, 2011 at 20:08

James

2931 silver badge5 bronze badges

Collectives™ on Stack Overflow

MySQL: How to count from columns separately?

4 Answers 4

5 Comments

2 Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

5 Comments

2 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related