1

I have a Voters table, which has fields including party and email.

party takes just a few acceptable values. I have a query which counts party membership and groups by the party:

SELECT party as Party, COUNT(*) AS Party_COUNT
FROM Voters
GROUP BY party
ORDER BY Party_COUNT DESC;

Works nicely. I would now like to show additional data: how many of those party members have an email address recorded in the DB?

I should end up with something like the following:

Party   Party_COUNT EMail
DEM     90802       10000
REP     41383       5000
NPP     40000       4750
...etc.

I get the aggregate email count easily with:

SELECT COUNT(*) email from Voters WHERE email <> ''

but I'm having trouble mashing my two count queries together to get the desired tabular result.

This is in MariaDB 10.1.18.

I am counting WHERE email <> '' because I receive the dataset from a governmental agency which can't even provide the DDL for the database, just a CSV dump.

The field seems to have only NULLs or actual e-mail addresses, but I can't be sure, especially as this data is updated somewhat frequently. Furthermore, CSV will not properly reflect NULLS, so empty strings are imported . . .

3

To count rows:

COUNT(*)

To count non-null values in a column:

COUNT(email)

To count non-null values, without counting the empty strings:

COUNT(CASE WHEN email > '' THEN email END)    -- or
COUNT(CASE WHEN email > '' THEN 1 END)        -- or
SUM(email > '')                               -- or
COUNT(email) - SUM(email = '')
1

Answer:

SELECT party as Party, COUNT(*) AS Party_COUNT, COUNT(email) - SUM(email = '') AS EMail_COUNT
FROM Voters
GROUP BY party
ORDER BY Party_COUNT DESC;

I am embarrassed to say that my MySQL client was not properly updating query results and that was the reason I was having unexpected results.

I am marking my own answer as correct. ypercube gave good info regarding usage of COUNT, but did not answer the key question which was:

"I'm having trouble mashing my two count queries together"

As I expected (and initially tried) the code in this answer could simply append "COUNT(email) AS EMail_COUNT" to the SELECT statement for the correct result.

Even better (thanks ypercube!) is to use "COUNT(email) - SUM(email = '')" as in the answer. This results in the result set showing where there are no results for e-mail but there are results for party registration.

If I had the requisite sleep the night before yesterday's question, I probably would have never landed here! ;-)

Thanks all!

  • You can mark you own answer, that's fine. I really thought that adding one more thing in the SELECT list was trivial, if one knows what to add. And note that COUNT(email) will count all non-null values, including empty strings. – ypercubeᵀᴹ Oct 16 '16 at 23:28
  • I agree it's trivial. But an answer should be simple and unambiguous for even database newbies to understand, if possible. You are right that empty strings will be counted in my answer. After I posted my question I rewrote the import script to convert empty string to NULL (which worked fine for the data-set in question). That conversion formed part of what pointed me to having a problem with my MySQL client! I'll update my answer to reflect a more correct count considering empty strings. – FBachofner Oct 17 '16 at 0:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.