CODEWITHSUNDEEP
mysqlsql
Buksy
Buksy

Reputation: 12238

Count multiple columns

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

Upvotes: 2

Views: 74

Answers (4)

Jakub Matczak
Jakub Matczak

Reputation: 15696

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;

Upvotes: 4

aaron-bond
aaron-bond

Reputation: 3349

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.

Upvotes: 0

Andomar
Andomar

Reputation: 238296

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'

Upvotes: 1

vidang
vidang

Reputation: 1771

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

Upvotes: 1

Related Questions