How to select count from a table based on some condition. I have a table contains some student details and exam_id.Here is my table model
const studentEntityModel=[
{
exam_id:1,
NAME:'JOHN DOE',
SUBJEJCT:'foo',
REGISTER_NO:123,
CONDONATION:'YES',
CONDONATION_STATUS:'PENDING',
},
{
exam_id:1,
NAME:'JOHN DOE',
subject:'foo-BAR',
REGISTER_NO:123,
CONDONATION:'YES',
CONDONATION_STATUS:'PENDING',
},
{
exam_id:1,
NAME:'JJ JOHN',
subject:'foo',
REGISTER_NO:215,
CONDONATION_STATUS:'APPROVED',
},
{
exam_id:1,
NAME:'JJ JOHN',
subject:'foo-BAR',
REGISTER_NO:215,
CONDONATION_STATUS:'REJECTED',
}
]I need to get unique exam_id's along with count of,
1)Total number of students
2)Number of students who has condonation status of PENDING
3)Number of students who has condonation status of APPROVED
4)Number of students who has condonation status of REJECTED.
I tried this way,
this.studentEntityModel
.createQueryBuilder('student')
.select('COUNT(DISTINCT student.REGISTER_NO)AS TOTAL_NO')
.select('COUNT( student.REGISTER_NO)AS PENDING')//i need to add condition here STATUS='Pending'
.select('COUNT(DISTINCT student.REGISTER_NO)AS APPROVED')//STATUS='APPROVED'
.select('COUNT(DISTINCT student.REGISTER_NO)AS REJECTED')//STATUS='APPROVED'
.select(['student.exam_id'])
.groupBy(' student.EXAM_ID')
.getRawMany()But i am stuck while adding conditions.
Expected Output
const expectedResult=[{
exam_id:1,
TOTAL_NO:2,
APPROVED:1,
PENDING:1,
REJECTED:1,
}]Only total_no is working here. I am using typeorm.