SQL Count Columns in Row Equal to Specific Value

Question

I have a registration table with several columns that contain values like "in progress" or "submitted". I'm trying to count how many of these columns are equal to "submitted", so I can determine a percent completion for the user. The query below obviously only returns 0 or 1, because it's querying only one column, but how can I incorporate the others?

For example, I want to count how many of the following columns are equal to "submitted" or "accepted":

network_user_status
work_ed_status
chores_status
class_schedule_status



SELECT COUNT(id) FROM registration WHERE username = "ajackson" AND enrollment_agreement_status = "accepted" OR enrollment_agreement_status = "submitted"

Gordon Linoff · Accepted Answer · 2014-04-28 18:03:17Z

2

First, your query is not doing what you think it is doing. The username condition is only applying to "accepted". I'm pretty sure you mean:

SELECT COUNT(id)
FROM registration
WHERE username = 'ajackson' AND enrollment_agreement_status in ('accepted', 'submitted')

If you want to count one or the other or both values, use conditional aggregation. This is easy in MySQL:

select count(id), sum(enrollment_agreement_status = 'accepted') as accepted,
       sum(enrollment_agreement_status = 'submitted') as submitted
from registration
where username = 'ajackson';

answered Apr 28, 2014 at 18:03

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

2 Comments

Adam Over a year ago

Thanks for the clarification there, but how do I incorporate the other columns?

Gordon Linoff Over a year ago

You need to edit your question with sample data and desired results.

Scotch · Accepted Answer · 2014-04-28 18:09:36Z

0

count doesn't depend on the number of columns you reference in your query. It returns all of the rows of your table that meet the conditions you specity. It's returning 0 or 1 probably because there's only one "ajackson" registration.

You probably want something like this.

select t1.numaccepted, t2.numsubmitted, (t1.numaccepted / t2.numsubmitted) as    YourPercent FROM
     (Select username, count(*) as numaccepted from registration where     enrollment_agreement_status = "accepted" GROUP BY username) t1, (select username,count(*)  as numsubmitted from registration where enrollment_agreement_status = "submitted" GROUP BY  username) t2

answered Apr 28, 2014 at 18:09

Scotch

3,23211 gold badges41 silver badges51 bronze badges

Comments

Cherree · Accepted Answer · 2014-04-28 18:17:00Z

Try something like... SELECT enrollment_agreement_status, COUNT(*) FROM registration WHERE username = "ajackson" AND enrollment_agreement_status = "accepted" OR enrollment_agreement_status = "submitted" GROUP BY id;

The two important parts of this query are what you're selecting and what you want to group by. In this case the query returns a status in the first column and the number of times the status occurs in the table in the second column. The two important parts of the query which cause this to happen are "enrollment_status_agreement, COUNT(*)" following the select and "GROUP BY enrollment_agreement_status" at the very end. The select fields are fairly obvious but "group by" combines identical results for the specified non-COUNT() fields. This means if you do something like "GROUP BY field1, field2" it would return a row for each unique combination of values in the two fields.

Shittu Joseph Olugbenga · Accepted Answer · 2015-02-17 15:53:30Z

0

You can use this:

COUNT(CASE WHEN value = 'submitted' THEN 1 ELSE NULL END) AS SubmittedCount

This will return a column SubmittedCount with the total count of the values within the table with text submitted.

answered Feb 17, 2015 at 15:53

Shittu Joseph Olugbenga

6,4865 gold badges30 silver badges39 bronze badges

Collectives™ on Stack Overflow

SQL Count Columns in Row Equal to Specific Value

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related