CODEWITHSUNDEEP
phpsql
SoftwareDev
SoftwareDev

Reputation: 356

SQL select query to find count from given table

I have one table in database -> applicants_detail which has two column
applied_personid and applied_courseid.
College provides number of courses.
I have the given id to applied_courseid, that is written like this:
arts--1
science--2
maths--3...
And applied_personid which contains applied person id.
I need to count how many people applied for the course, like in the page it should show:
maths------15 people_applied
science------20 people_applied
I tried this query: 

$query="select * from people where people_id in(select applied_personid from applicants where applied_courseid  =".$_GET['postcourseid']." )";

And the code to find count is not able to show count in the table. 

<?php
            $count=0;
                while($row=mysql_fetch_array($res))
                {

                    echo '<tr> <td width="10%">'.$count.'
                            <td width="50%">'.$row['student_fnm'].'
                                                            <td width="50%">'.$row['applied_courseid'].'
                            <td width="30%"><a href="'.$row['course_name'].'">course name</a>
                            ';
                        $count++;
                }


            ?>

Upvotes: 0

Views: 117

Answers (2)

Riad
Riad

Reputation: 3850

Try this to get all data:

    SELECT course_name, applied_courseid as course_id, count(applied_personid) as `student_number`
    FROM applicants_detail 
    INNER JOIN course_detail ON course_detail.course_id = applicants_detail.course_id
    GROUP BY applied_courseid

Upvotes: 1

peter.petrov
peter.petrov

Reputation: 39457

You just need to group by applied_courseid. This should do it.

    select applied_courseid, count(applied_personid) as `count`
    from
    applicants_detail
    group by applied_courseid

Then if needed use the results here to join to the other tables
which you probably have (that would give the you course name e.g.).

Upvotes: 2

Related Questions