Reputation:

count in select query

I have the following query:

select exam_id,semester_id from marks where student_id=150  and semester_id<=1914;

Its output is:

exam_id     |    semester_id

   27               1913
   68               1913
   64               1914

I want to find out the count of similar semester_id.

when I try mysql_num_rows it shows 3.

But I want output like this

 semester_id 1913---------->count 2
 semester_id 1914---------->count 1

Upvotes: 5

Answers (5)

Reputation: 1231

Use count()

SELECT semester_id, COUNT(*) as count
FROM marks  
WHERE student_id=150 
AND semester_id<=1914
GROUP BY semester_id;

Upvotes: 1

Reputation: 11

Select semester_id, count(exam_id)
From marks
Group By semester_id;

if you feel the need, use:

count(distinct exam_id)

Upvotes: 1

Reputation: 12788

You just use GROUP BY:

SELECT semester_id, COUNT(*) as count
 FROM marks  
 WHERE student_id=150 
  AND semester_id<=1914
 GROUP BY semester_id;

Upvotes: 6

Reputation: 2785

Use Count() aggregate function you can achieve this.

 SELECT semester_id, COUNT(exam_id)  FROM marks  
 WHERE student_id=150  AND semester_id<=1914
 GROUP BY semester_id;

Upvotes: 3

Reputation: 4370

try this

select semester_id,count(*) as cnt 
from marks 
where student_id=150 and semester_id<=1914 
group by semester_id;

Upvotes: 3