Count multiple columns

Question

I have this table:

Teams:
ID | Size1 | Size2 | Size3 | Size4 | ... (other columns)..
----------------------------------------
1  |  L    |  XL   |   L   |   M   | ...
----------------------------------------
2  |  S    |  L    |   S   |  XXL  | ...
----------------------------------------
3  |  M    |  XXL  |   L   |   M   | ...
----------------------------------------
4  |  L    |  XL   |  XS   |  XXL  | ...

What is the most effective (and simplest) MySQL query to count all L in table?

I'd like to have only one field in result which is the count of all L in all columns.

EDIT: Just to clarify little more, in my table there is 152 Ls in first column, 2 in second, 151 in third and 3 in fourth and I expect 308 as result

SELECT ( SELECT COUNT(*) FROM Teams WHERE Size1='L' ) + ( SELECT COUNT(*) FROM Teams WHERE Size2='L' ) + ( SELECT COUNT(*) FROM Teams WHERE Size3='L' ) + ( SELECT COUNT(*) FROM Teams WHERE Size4='L' ) + ( ... ); — Alexander
– Alexander, Commented Feb 4, 2014 at 12:45
consider normalizing size fields to their own table - then only have to do single count on that - and not have to change table if you have another size. Otherwise Alexander's answer is the way to go. — Ruskin
– Ruskin, Commented Feb 4, 2014 at 12:47
@Buksy now you need to check all queries on perfomance, and give the final answer — Alexander
– Alexander, Commented Feb 4, 2014 at 12:51
And, btw, if this is the table that shows availability of the clothes of each size, this architecture isn't the best one. — Alexander
– Alexander, Commented Feb 4, 2014 at 12:53

Jakub Matczak · Accepted Answer · 2014-02-04 13:08:46Z

4

You can use SUM and CASE to do that:

SELECT 
  sum(case when Size1 = 'L' then 1 else 0 end) +
  sum(case when Size2 = 'L'  then 1 else 0 end) +
  sum(case when Size3 = 'L'  then 1 else 0 end) +
  sum(case when Size4 = 'L'  then 1 else 0 end)
FROM Teams;

Alternatively instead of CASE, you can use IF:

SELECT 
  sum(IF(Size1 = 'L',1, 0)) +
  sum(IF(Size2 = 'L',1, 0)) +
  sum(IF(Size3 = 'L',1, 0)) +
  sum(IF(Size4 = 'L',1, 0))
FROM Teams;

This is actually the same.

Edit.

According to Andomar's comment there's even simpler solution:

SELECT 
  sum(Size1 = 'L') +
  sum(Size2 = 'L') +
  sum(Size3 = 'L') +
  sum(Size4 = 'L')
FROM Teams;

This is correct since true is equal to 1 in MySQL. I've just verified this. ;-)

2nd Edit

Next step to simplify this - only one SUM usage:

   SELECT sum(
      (Size1 = 'L') + 
      (Size2 = 'L') +
      (Size3 = 'L') +
      (Size4 = 'L') )
    FROM Teams;

edited Feb 4, 2014 at 13:08

answered Feb 4, 2014 at 12:48

Jakub Matczak

15.7k5 gold badges52 silver badges68 bronze badges

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

2 Comments

Andomar Over a year ago

Since in MySQL true is 1, a shorter version would be sum(size = 'L'). Example at SQL Fiddle.

Buksy Over a year ago

you have typo in parentheses in second Query, I cant edit it :)

vidang · Accepted Answer · 2014-02-04 12:48:05Z

1

Please try the following query:

SELECT sum( 
  IF(Size1='L', 1, 0) + 
  IF(Size2='L', 1, 0) + 
  IF(Size3='L', 1, 0) + 
  IF(Size4='L', 1, 0)
) as total FROM Teams

answered Feb 4, 2014 at 12:48

vidang

1,78113 silver badges13 bronze badges

Comments

Andomar · Accepted Answer · 2014-02-04 12:57:15Z

1

Assuming there can only be one 'L' per row:

select  count(*)
from    YourTable
where   'L' in (Size1, Size2, ..., SizeN)

Or a normalizing solution, which supports multiple 'L''s per row:

select  count(*)
from    (
        select  size1 as size
        from    YourTable
        union all 
        select  size2
        from    YourTable
        union all 
        select  size3
        from    YourTable
        union all 
        ...
        )
where   size = 'L'

edited Feb 4, 2014 at 12:57

answered Feb 4, 2014 at 12:47

Andomar

239k55 gold badges387 silver badges412 bronze badges

6 Comments

aaron-bond Over a year ago

Can you do value in column like that? That's news to me

Andomar Over a year ago

@Askanison4: a in (b,c,d) is just syntactic sugar for a = b or a = c or a = d

Andomar Over a year ago

@dragoste: Correct. I assumed it wouldn't make sense to have two sizes called L for the same product, and the example data agrees with that.

Jakub Matczak Over a year ago

@Andomar we're not here to decide what makes sense in authors task. There are two Ls in first row in provided example data.

Buksy Over a year ago

The first query doesnt work to me SELECT count( * ) FROM table WHERE 'L' IN (size1, size2, size3, size4) returns 152, while there is 152 Ls in first column, 2 in second, 151 in third and 3 in fourth

|

aaron-bond · Accepted Answer · 2014-02-04 12:58:38Z

0

Your WHERE condition is going to be quite large but you could use this:

SELECT count(Size1) + count(size2) AS total WHERE size1 = 'L' OR size2 = 'L'

This solution will count all columns and total based on individual column comparisons.

edited Feb 4, 2014 at 12:58

answered Feb 4, 2014 at 12:47

aaron-bond

3,3494 gold badges28 silver badges41 bronze badges

2 Comments

Andomar Over a year ago

This should count rows twice even if there is only one L

aaron-bond Over a year ago

@Andomar Yes this is his expected result.

Collectives™ on Stack Overflow

Count multiple columns

4 Answers 4

2 Comments

Comments

6 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

6 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related