CODEWITHSUNDEEP
mysqlselectcount
theangryhornet
theangryhornet

Reputation: 403

MySQL: How to count from columns separately?

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...

Upvotes: 5

Views: 214

Answers (4)

Joe Stefanelli
Joe Stefanelli

Reputation: 135808

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'

Upvotes: 6

James
James

Reputation: 293

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

Upvotes: 1

Sam Dufel
Sam Dufel

Reputation: 17598

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"

Upvotes: 2

Vitor Furlanetti
Vitor Furlanetti

Reputation: 451

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"

Upvotes: 1

Related Questions