Win a copy of Practical SVG this week in the HTML/CSS/JavaScript forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic

How do I format this query using JSP?

 
Chuan Ren
Ranch Hand
Posts: 42
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have three tables
------------------------
BS_SESSION
---------------
SESSION_ID
SESSION_DATE
BS_GROUP
-----------------
GROUP_ID
BS_CANDIDATE
-------------------
CANDIDATE_ID
GROUP_ID (FK)
BS_REGISTRATION
-----------------------
REGISTRATION_ID
CANDIDATE_ID (FK)
SESSION_ID (FK)
REGISTRATION_DATE

I would like to get some statistics based on group as below:
Group 1 : 23
Group 2 : 0
Group 3 : 12
based on the record in BS_REGISTRATION in the time interval from SESSION_DATE.
This is the SQL statement which I got from mySQL and it listed a few rows with their respective count.
select count(REGISTRATION_ID) from BS_REGISTRATION rg, BS_CANDIDATE c, BS_GROUP a, BS_SESSION s
where s.SESSION_DATE between '2000-10-10' AND '2550-10-1'
and rg.CANDIDATE_ID = c.CANDIDATE_ID
and a.GROUP_ID = c.GROUP_ID
and rg.SESSION_ID = s.SESSION_ID
group by c.GROUP_ID
But how do I get JSP to display it in Group 1, 2, 3 format? I was thinking to use a FOR loop, but does not go very far at all. I just wonder if there is a better way of doing this.
Thank you.
 
Tim Baker
Ranch Hand
Posts: 541
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
change this:
select count(REGISTRATION_ID) from ...
to this:
select a.GROUP_ID, count(REGISTRATION_ID) from ...
this will give you two columns with the group id against the count like you showed above. hope this is what you wanted
 
Chuan Ren
Ranch Hand
Posts: 42
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Tim,
Thanks for the solution. It is exactly what I needed. Thanks.
 
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!