• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

sum of rows

 
Greenhorn
Posts: 29
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I am developing an attendance management system..

I have a table with the following columns :
RollNo(int) Day1(boolean) Day2(boolean Day3......
-----------------------------------------------------------------
123 0 1 1
321 1 1 0

0 is for absent and 1 is for present

The days are added each day dynamically in the database..so the number of columns is not fixed....What i need is to find the total attendance against a roll no. For eg- for rollno 123, the total attendance should come out to be 2. Is it possible to do that?
 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
This isn't how relational databases were designed to work. You'd need a code that would look at columns existing in the table and compute the sums dynamically, and that's probably next to impossible in pure SQL.

Your table should contain the columns RollNo, DayNo and Attendance. You'd then get the sum of attended days using simple GROUP BY query:Also, Oracle doesn't have booleans in SQL, so the Attendance column should really be NUMBER(1,0) with a check constraint allowing only values 0 or 1.
 
neelesh kumar
Greenhorn
Posts: 29
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
What if instead of having the columns as boolean, i make it int...nd then increment the value by 1 everytime a person is marked present....For eg-

RollNo(int) Day1(int) Day2(int) Day3......
-----------------------------------------------------------------
123 0 1 2
321 1 2 2

Then the total attendance will be same as the values in the last column nd i will just need to display values of last column...can that be done?

P.S.- Thanks for the quick reply btw
 
Martin Vashko
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
But the structure of the table remains the same, and so does the problem with dynamic columns. Getting a sum over dynamic columns will be hard, and it will probably perform worse than the solution I've suggested. Is that solution clear to you?

The change from boolean to number is independent from the issue of dynamic columns. It's a new question actually. I'd suggest to solve one issue at a time.
 
neelesh kumar
Greenhorn
Posts: 29
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Actually i am developing a windows phone application wherein i require this database...i am already so far ahead with it, and i will need to make a lot of changes to all the modules, if i am to change the structure of the table.....thats why i wanted a solution where the columns remain the same as they are now...but if thats not possible, i will start afresh
 
Martin Vashko
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

neelesh kumar wrote:i am already so far ahead with it, and i will need to make a lot of changes to all the modules, if i am to change the structure of the table...


Yeah, that's unfortunate. But it is also a sign that you perhaps haven't designed your application in the best possible way. I'd suggest taking this as a possibility to learn how to structure your application better.

You could avoid reingeneering your application by creating the query dynamically, such asThis assumes that you somehow know the number of columns you currently have in the table.

I'd still suggest doing it the right way, though. Adding columns to the table dynamically is really horrible. It would help if you could at least place some limit on the number of days (say, 30), and create columns for all such days. This would allow you to create a static query like

Actually i am developing a windows phone application wherein i require this database..


You're posting in the Oracle forum. Are you really using the Oracle database?
 
neelesh kumar
Greenhorn
Posts: 29
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Yes..i am using oracle database and have connected the application to it....anyways, i will put a limit to the number of columns to make it static...thanks!
 
Hey, check out my mega multi devastator cannon. It's wicked. It makes this tiny ad look weak:
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic