0

Is there is query with which I can get the sum of elements in one row?

I am trying to develop an atttendance application. Design as follows-

<date1> <date2> <date3> <date4>--- <attended>
<rollno1> 1 0 1 1 3
<rollno2> 0 0 1 1 2
<rollno3> 0 1 0 0 1

I am trying to get the attended field.

Is it possible, or is it a bad design?

Please suggest an alternate design if so.

I am absolutely new to database design.

2
Contributors
1
Reply
2
Views
9 Years
Discussion Span
Last Post by tesuji
0

Design as follows-

<date1> <date2> <date3> <date4>--- <attended>
<rollno1> 1 0 1 1 3
<rollno2> 0 0 1 1 2
<rollno3> 0 1 0 0 1

How to design the appropriate select statement? Also, what if you want to add a further date <date5>? From a theoretical point of view, your design even violates the first normal form (1NF), because of the repeating date-x group. You will run into serious difficulties by that design. Your table might be considered to be badly designed.
Simple rule: if you are forced to expand the number of columns when new data need to be recorded, you must put this sort of data into rows, and not in columns! So a rather better design is:

table: roda
roll#     dates
-------------------
rollno1   date1
rollno1   date3
rollno1   date4
rollno2   date3
rollno2   date4
rollno3   date2

This arrangement is much more flexible (also fulfils 1NF, 2NF, 3NF, BCNF, where 3NF is the appropriate normal form a table always should meed. You need to learn something about Codd's normalization theorie if you want to design good tables).

For example: select attended:
select roll#, count(roll#) as attended from roda group by roll#
will give:

roll#       attended
-----------------
rollno1   3
rollno2   2
rollno3   1

Further advice: Tables always must have (yes, they must have that!) primary key, for example primary key(roll#, dates).

Hope this will help you.

krs,
tesu

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.