counting in table

[edujobs2k18]

Hi,

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.

[douvega]

Hi,

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,

Regards.

[edujobs2k18]

Hi,

 

Yes. I am still unclear. My table name is Student_Master. Please provide some simple SQL query. It will help me.

 

Thanks & Regards.

[douvega]

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.

Regards.

[edujobs2k18]

Hi Douvega,

 

What is Batch_Rel_ID? can i contact with you via mail? My mail ID is tanmoy.m@jobsacademy.co.in. 

[Corpcat]

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! 

Division	Grace Barcode No
(blank)	3
BJC Defence DMM NSW	1171
Total	38893

Show per page  102550100250		Page     of 156
	Records 1-250 of 38893