addition having null values in between
-
I am designing a database for students management. For flexibility reasons I have kept it on a monthly basis wherein the students are awarded the number of lectures they have attended and zero if not. the department doesn't follow a strict time table hence there are times when there is no lecture for that subject during the entire day. The default value is null for such scenario. In short for each subject a student gets as following: If present - number of lectures attended if absent - zero if no lectures for the day - NULL. The problem is when adding up the entire column(yes column) if there is a null value the final answer is null. I want the answer to be summation of all the numbers present and Not count in the NULL values.
-
I am designing a database for students management. For flexibility reasons I have kept it on a monthly basis wherein the students are awarded the number of lectures they have attended and zero if not. the department doesn't follow a strict time table hence there are times when there is no lecture for that subject during the entire day. The default value is null for such scenario. In short for each subject a student gets as following: If present - number of lectures attended if absent - zero if no lectures for the day - NULL. The problem is when adding up the entire column(yes column) if there is a null value the final answer is null. I want the answer to be summation of all the numbers present and Not count in the NULL values.
How do you add up the values? SUM should give you the correct result with NULL values. What database engine do you use?
-
I am designing a database for students management. For flexibility reasons I have kept it on a monthly basis wherein the students are awarded the number of lectures they have attended and zero if not. the department doesn't follow a strict time table hence there are times when there is no lecture for that subject during the entire day. The default value is null for such scenario. In short for each subject a student gets as following: If present - number of lectures attended if absent - zero if no lectures for the day - NULL. The problem is when adding up the entire column(yes column) if there is a null value the final answer is null. I want the answer to be summation of all the numbers present and Not count in the NULL values.
-
How do you add up the values? SUM should give you the correct result with NULL values. What database engine do you use?
create or replace view Total_TABLE as
select 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,29,30,31,1+2+3+4+5+6+7+8+9+10+11+12+13+14+15+16+17+18+19+20+21+22+23+24+25+26+27+28+29+29+30+31 as Total
from Add_attend;the problem is when there is a null value in the table the final result after adding is Null and I want the result of all all the numbers and NOT null value. I am using mysql wampserver
-
If this is Microsoft SQL server you can easily use IsNull() or COALESCE().
There are only 10 types of people in the world, those who understand binary and those who don't.
I got your point sir. I know i am an inexperienced newbie, this the first time I am doing this. It would be of great help if you can help me with this. I am using Mysql server
-
I got your point sir. I know i am an inexperienced newbie, this the first time I am doing this. It would be of great help if you can help me with this. I am using Mysql server
-
Member 11840363 wrote:
It would be of great help if you can help me with this.
I did. :confused:
There are only 10 types of people in the world, those who understand binary and those who don't.
10 is TWO in binary. can you please help me in framing the correct query? Please
-
10 is TWO in binary. can you please help me in framing the correct query? Please
Did you lookup how to use IsNull or COALESCE()? It's very simple. SELECT COALESCE(field1, 0) or SELECT IsNull(field1, 0) If field1 is null then you will get 0. So, if you are adding them up then adding 0 will not get counted.
There are only 10 types of people in the world, those who understand binary and those who don't.
-
create or replace view Total_TABLE as
select 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,29,30,31,1+2+3+4+5+6+7+8+9+10+11+12+13+14+15+16+17+18+19+20+21+22+23+24+25+26+27+28+29+29+30+31 as Total
from Add_attend;the problem is when there is a null value in the table the final result after adding is Null and I want the result of all all the numbers and NOT null value. I am using mysql wampserver
-
Thanks a lot sir. it has been solved :)