MySQL How to count columns containing specified column value

Question

I have the table xyz

ID    day1    day2    day3    day4
1     A         A       P       P
2     A         A       A       P
3     A         A       A       A
4     A         P       P       P

I want to be able to query this and return the ID with the number of columns that have A as their value in that row. So the result would look like this:

ID    Count
 1     2
 2     3
 3     4
 4     1

So you want to pass a value (in this case A) as a condition to your query? — P.Salmon
– P.Salmon, Commented May 22, 2018 at 6:11

Tim Biegeleisen · Accepted Answer · 2018-05-22 06:07:39Z

3

Here is a trick you can use involving string concatenation:

SELECT
    ID,
    CHAR_LENGTH(CONCAT(day1, day2, day3, day4)) -
    CHAR_LENGTH(REPLACE(CONCAT(day1, day2, day3, day4), 'A', '')) AS Count
FROM yourTable
ORDER BY ID;

Demo

If you want to count other letters as well, just duplicate the logic I have for the letter A, e.g. for L we can try:

CHAR_LENGTH(CONCAT(day1, day2, day3, day4)) -
CHAR_LENGTH(REPLACE(CONCAT(day1, day2, day3, day4), 'L', ''))

answered May 22, 2018 at 6:07

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

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

6 Comments

Michał Turczyn Over a year ago

I think it would be good to place concatenation in subquery and use its results multiple times in outer query. Wouldn't it be more efficient? IMO, it would look cleaner and more concise I guess :)

Tim Biegeleisen Over a year ago

@MichałTurczyn I have no idea what you mean. The replacement would have to be run separately for each letter, there is no way around this that I can see (short of writing a UDF).

Michał Turczyn Over a year ago

Well, doing it the way you suggested, you have to invoke concatenation 2 times for each letter. Doing it in a subquery would result in invoking CONCAT method once. But I definietely agree that is correct query to get the desired results :) +1

Tim Biegeleisen Over a year ago

@MichałTurczyn I see no reason to use a subquery here, whose overhead would likely greatly outweigh any savings you are suggesting.

Michał Turczyn Over a year ago

Just to be clear: select char_length(concat(...)) - char_length(concat(...)) from tbl compared to select char_length(col) - char_length(replace(col)) from (select concat(...) as col from tbl) as innerTbl.

|

Michał Turczyn · Accepted Answer · 2018-05-22 06:10:29Z

2

This is simple to accomplish applying case to all columns and usmming them up! Try below:

select id,
       case day1 when 'A' then 1 else 0 end +
       case day2 when 'A' then 1 else 0 end +
       case day3 when 'A' then 1 else 0 end +
       case day4 when 'A' then 1 else 0 end `CountA`,
       case day1 when 'L' then 1 else 0 end +
       case day2 when 'L' then 1 else 0 end +
       case day3 when 'L' then 1 else 0 end +
       case day4 when 'L' then 1 else 0 end `CountL`,
       case day1 when 'P' then 1 else 0 end +
       case day2 when 'P' then 1 else 0 end +
       case day3 when 'P' then 1 else 0 end +
       case day4 when 'P' then 1 else 0 end `CountP`
from Tbl

edited May 22, 2018 at 6:10

answered May 22, 2018 at 6:04

Michał Turczyn

41.2k18 gold badges58 silver badges87 bronze badges

5 Comments

karan sharma Over a year ago

Thanks Michal .I Also the number of columns that have A L and P as their value in that row

Michał Turczyn Over a year ago

@karansharma See updated answer :) Also accept it, if it solved the rpoblem.

Michał Turczyn Over a year ago

@TimBiegeleisen I suppose that I filled all OPs requests, but you are totally right and that's why I used if statement :)

karan sharma Over a year ago

Thanks Michal Its Woks

Michał Turczyn Over a year ago

@karansharma You have to valid answers, you should accept one of them :)

Collectives™ on Stack Overflow

MySQL How to count columns containing specified column value

2 Answers 2

Demo

6 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related