CODEWITHSUNDEEP
mysql
Alex Mathew
Alex Mathew

Reputation: 1554

How to find total no of '1' in the table?

Hey Friends
Here is the table details and data

id data h1 h2 h3 h4 h5 h6 h7 h8
1 2010-10-10 1 1 1 1 1 1 1 1 
1 2010-10-11 1 0 1 0 1 0 0 1
1 2010-10-12 0 1 1 1 1 0 0 0

what i need to know is how can i create query for following

  1. find total no of zero in h1-h8 for id 1
  2. find total no of one in h1-h8 for id 1
  3. find total of data in h1-h8 for id1(total no of zero+total no of one)
  4. after getting 1,2,3 use the following answer in the equation (val(3)-val(1))/val(3)

i am not that much good in mysql, if you guys can help me then it will be a great help,thks in advance

Upvotes: 1

Views: 123

Answers (2)

Branimir
Branimir

Reputation: 4367

Choosing the right data structure is very important. You can change you table definition to something like this:

id data        attribute value
1  2010-10-10  h1        1
1  2010-10-10  h2        1
...

Now you can use simple queries like:

1) SELECT COUNT(*) FROM table WHERE id = 1 AND value = 0
2) SELECT COUNT(*) FROM table WHERE id = 1 AND value = 1
3) SELECT COUNT(*) FROM table WHERE id = 1

Upvotes: 2

Daniel Mošmondor
Daniel Mošmondor

Reputation: 19956

First of all, it seems like answer to 3. will be 8*number of rows, but...

answer 1:

SELECT SUM (8-(h1+h2+h3+h4+h5+h6+h7+h8)) FROM table WHERE id=YourID

answer 2:

SELECT SUM (h1+h2+h3+h4+h5+h6+h7+h8) FROM table WHERE id=YourID

answer 3:

SELECT SUM (8 as x) FROM table WHERE id=YourID

and answer 4:

figure it out yourself, you should have enough to start your journey now :)

Upvotes: 3

Related Questions