Jump to content
  • 0

counting in table




i have a table. Which is contain 10 training centres. In each training centre there are 7/8 courses. Each course have 4/5 batches. Each batch contain 30 student. There are training_status column in the table where students have different values like pass, fail,  not appeared and ongoing. I want to count how many pass, fail, not appeared and ongoing in each batch. It is coming in good way in pivot (since i dont know better reporting format for same)

Now i want to add more field in row. Like after getting pass in training candidate got jobs/ placement. And after placement they used to submit proof of placement (Offer letter or Joining letter). They have submitted the proof of working (Payslips and bank statement) for continuous three months . 

Now i want to count how many candidates got placement out of pass, how many offer letter submitted. How many payslips for first month, second month and third month for batch-wise in a single report which is very easy in excel. But very difficult in caspio. I have shared the screenshot.

pivot issue.JPG

Link to comment
Share on other sites

5 answers to this question

Recommended Posts

  • 0


You can accomplish that by using Calculated Fields with SQL subqueries in your report.

Please check this article for more info about this approach.

Just in case you are still unclear about it after reviewing the above article, you can share the name of the tables or views you would use for the calculations so we may help you with a sample SQL query.

I hope this helps,


Link to comment
Share on other sites

  • 0

Let me give you a general flow, remember that a strong backend structure will allow you to manage your app correctly.

From the case you have exposed, your should have the following tables:

  • Training_Centers
  • Courses
  • Batches
  • Users
  • Batch_per_Student

Then, table relationships properly set up, where 1 training center may have several courses, etc. Remember this is a suggested structure, you may have it built already in such case I would have to get more details about it.

You should base your datapage in a view that Displays the batches and then include a calculated field in the datapage with the following sql for counting passed students:

SELECT COUNT(Training_Status) FROM STUDENTS_MASTER WHERE Training_Status='Pass' AND Batch_Rel_ID='[@the batch id field]'

If you need another column with failed students, then change the Training_Status='Pass' part for Training_Status='Failed' which is the filter to know what to count.

As mentioned above, depending on your tables structure, the query may change. Try to get the logic behind it so you can apply it to your specific case.

Don't hesitate to ask more questions if you are still unclear it, include any details so the answer can be more accurate.


Link to comment
Share on other sites

  • 0

I am also have some problems with a Report data page that I want to group by a field and count the number of records for that grouped field

When I use the count function and collapse the group I still have to page through the 38000 records rather than getting a neat summary table

There are 16 Divisions and I want the Aggregate counts to appear all on one page! 

DivisionAscending Grace Barcode No
[+] (blank) 3
[+] BJC Defence DMM NSW 1171
Total 38893
Show per page  102550100250   
 Page     of 156  Next Last
Records 1-250 of 38893



Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...