How to get count of distinct rows in MySQL?

Question

I have used the following query:

select tblclass.classname,tblattendance.id 
 from tblclass,tblattendance
where tblclass.classcode=tblattendance.classcode 
  and tblattendance.attdate='2013-07-01'

Output of this query is as follows:

enter image description here

Now what I want is rather than the above result I want count of different classes like IB-2,IC-5. Please tell me what modifications do I need to made in my query to get the desired result

thank you all.All the 3 answers worked for me:)

rupinder18
– rupinder18

2013-09-24 07:53:40 +00:00
Commented Sep 24, 2013 at 7:53 — rupinder18
– rupinder18, Commented Sep 24, 2013 at 7:53

Steve · Accepted Answer · 2013-09-24 07:48:18Z

3

Use the Group By SQL clause and add the aggregate function Count

select tblclass.classname, Count(tblattendance.id) as counter 
from tblclass,tblattendance 
where tblclass.classcode=tblattendance.classcode and tblattendance.attdate='2013-07-01'
group by tblclass.classname

answered Sep 24, 2013 at 7:48

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

rupinder18 Over a year ago

i have a table tblstudent now i want a field where i can get no of students present in tblattendance on given date and total no of students in that class

Steve Over a year ago

Please open a new question

naveen goyal · Accepted Answer · 2013-09-24 07:49:07Z

0

Try this

select count(tblattendance.id),tblclass.classname from tblclass,tblattendance 
where tblclass.classcode=tblattendance.classcode and tblattendance.attdate='2013-07-01' 
group by tblclass.classname

answered Sep 24, 2013 at 7:49

naveen goyal

4,6272 gold badges18 silver badges26 bronze badges

Comments

Himanshu · Accepted Answer · 2013-09-24 07:50:01Z

0

Use COUNT() function for that with GROUP BY. Also use JOIN.

SELECT tc.classname, COUNT(tc.classname) AS COUNTS
  FROM tblclass tc 
  JOIN tblattendance tt
    ON tc.classcode = tt.classcode
 WHERE tt.attdate='2013-07-01'
 GROUP BY tc.classname

answered Sep 24, 2013 at 7:50

Himanshu

32.8k32 gold badges117 silver badges137 bronze badges

Collectives™ on Stack Overflow

How to get count of distinct rows in MySQL?

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related