SQL query count = 0/null

Question

I have the following MySql table with ~19000 entries like this:

ID      USER        FIELD1         FIELD2         SOMEINT   ERROR
1       name1       null           null           null      missing...
2       name1       value1         value2         3         validated!
3       name1       value3         wrongvalue1    null      syntax
4       name2       wrongvalue2    value4         null      syntax
etc...................................................................

I would like to get a list like this:

USER    totalEntries     totalValid   totalMissing    totalSyntax
name1   3                1            1               1
name2   1                0            0               1
etc...................................................................

I have a query for every column like this:

select user, count(user) valid from table where 
someint is not null group by user limit 0, 20000;
(total valid entries)

select user, count(*) totalEntries from table group by user
limit 0, 20000; (total entries)

select user, count(*) totalMissing from table where field1 is null or
field2 is null group by user limit 0, 20000; (total Missing entrie)

select user, count(*) syntax from table where error like 'syntax%'
group by user limit 0, 20000 (syntaxerror entries)

The problem is that "group by" does not list the count(...) entries as

USER     valid
...
name3    0

So the 4 query results do not have the same rowcount. How can I solve this Problem?

What is count(responsible)? There is no 'responsible' in any of the data you showed before. — GolezTrol
– GolezTrol, Commented Aug 8, 2014 at 9:04
I don't understand your question. Your example is not clear to me. Try to explain better, please. — Paolo
– Paolo, Commented Aug 8, 2014 at 9:04

JCalcines · Accepted Answer · 2014-08-08 09:18:59Z

3

You are trying to do this:

SELECT user, COUNT(*) as totalEntries, 
    SUM(CASE WHEN someint IS NOT NULL THEN 1 ELSE 0 END),
    SUM(CASE WHEN field1 IS NULL OR field2 IS NULL THEN 1 ELSE 0 END),
    SUM(CASE WHEN error LIKE 'syntax%' THEN 1 ELSE 0 END)
FROM SomeTable
GROUP by user

User Name
Number of entries of the user
Number of entries of entries with some int different of NULL
Number of entries where Error ismissing`
Number of entries where ERROR is syntax

PD: Maybe you want to add the LIMIT 0,20000 at the end of the query. I didn't do that because I didn't get the purpose.

edited Aug 8, 2014 at 9:18

answered Aug 8, 2014 at 9:08

JCalcines

1,28612 silver badges27 bronze badges

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

3 Comments

JCalcines Over a year ago

You're welcome but, Why did you use the limit? I'm a little bit curious

Flexi Over a year ago

I was just trying this query out with MySQL Workbench and the default limit was at 1000. I know "quick and dirty" but I didn't have any time. ;)

JCalcines Over a year ago

Ok, :). Also you could remove the limit on File\Preferences.

Collectives™ on Stack Overflow

SQL query count = 0/null

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related