Select Multiple Values from different columns

Question

I have 5 different columns with over 100 rows each having an assortment of numbers.

I have been able to count all values from column 1 that equal 3

I want to be able to count all the number 3s from all 5 different columns and add them

$countquery = "SELECT COUNT(*) FROM PowerBall WHERE W1=3";
$countresult = mysql_query($countquery) or die(mysql_error());

while($countrow = mysql_fetch_array($countresult)) {
    echo "<br />";
    echo "There are ". $countrow['COUNT(*)']."-3s";
}

Please, don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO or MySQLi - this article will help you decide which. — Kermit
– Kermit, Commented Feb 22, 2013 at 22:13

Taryn · Accepted Answer · 2013-02-22 22:18:53Z

4

You can use an aggregate function with a CASE expression to get the total number of 3 values in all of your columns.

Select
  sum(case when col1 = 3 then 1 else 0 end) TotalCol1,
  sum(case when col2 = 3 then 1 else 0 end) TotalCol2,
  sum(case when col3 = 3 then 1 else 0 end) TotalCol3,
  sum(case when col4 = 3 then 1 else 0 end) TotalCol4,
  sum(case when col5 = 3 then 1 else 0 end) TotalCol5
from PowerBall

If you want this in one column, then you can use:

select TotalCol1 + TotalCol2 + TotalCol3 + TotalCol4 + TotalCol5
from
(
  Select
    sum(case when col1 = 3 then 1 else 0 end) TotalCol1,
    sum(case when col2 = 3 then 1 else 0 end) TotalCol2,
    sum(case when col3 = 3 then 1 else 0 end) TotalCol3,
    sum(case when col4 = 3 then 1 else 0 end) TotalCol4,
    sum(case when col5 = 3 then 1 else 0 end) TotalCol5
  from PowerBall
) src

Or even:

select sum(Total)
from
(
  Select count(col1) Total
  from PowerBall
  where col1 = 3
  union all
  Select count(col2)
  from PowerBall
  where col2 = 3
  union all
  Select count(col3)
  from PowerBall
  where col3 = 3
  union all
  Select count(col4)
  from PowerBall
  where col4 = 3
  union all
  Select count(col5)
  from PowerBall
  where col5 = 3
) src

edited Feb 22, 2013 at 22:18

answered Feb 22, 2013 at 22:13

Taryn

249k57 gold badges374 silver badges409 bronze badges

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

5 Comments

Kevin Nelson Over a year ago

you don't need a CASE statement, SUM(COL1=3) will suffice since it's already a boolean and will evaluate to either 1 or 0.

Taryn Over a year ago

@KevinNelson I use CASE because it is ANSI standard SQL. The SUM(COl1 =3) is MySQL specific syntax. :)

inVINCEable Over a year ago

Whats the TotalCol1 does it calculate or is it a variable

Taryn Over a year ago

@inVINCEable Those are the alias names for each of the columns created. The as is optional.

Kermit Over a year ago

TotalCol1 references the SUM calculation from the derived query.

RandomUs1r · Accepted Answer · 2013-02-22 22:15:10Z

1

SELECT SUM(CASE WHEN COL1 = 3 THEN 1 ELSE 0 END) AS 'Col1',
SUM(CASE WHEN COL1 = 3 THEN 1 ELSE 0 END) AS 'Col2',
....
 FROM PowerBall WHERE W1 is not null

answered Feb 22, 2013 at 22:15

RandomUs1r

4,2182 gold badges27 silver badges47 bronze badges

Collectives™ on Stack Overflow

Select Multiple Values from different columns

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related