CODEWITHSUNDEEP
mysqldatabase
karthik
karthik

Reputation: 63

getting count values in mysql

I have a table with 3 columns and looks like

branchid  semesterid  courseid
 4          25           2        
 4          36           5
 4          23           3
 4          12           10 
 4          34           15
 4          2            7
 4          23           42

 7          23           9
 7          10           6
 7          34           3
 7          20           17

I need the count of semesterid and courseid of that branch

SELECT branchid, count(semesterid), count(courseid) WHERE branchid = 4

When I execute this query, the count(semesterid) shows wrong count and courseid shows correct count of 7

Upvotes: 0

Views: 80

Answers (3)

phlogratos
phlogratos

Reputation: 13924

If you want the number of unique results you have to replace count(id) with count(distinct id)

Upvotes: 0

Joe G Joseph
Joe G Joseph

Reputation: 24046

try:

SELECT branchid, 
       count(distinct semesterid) as semesterid, 
       count(distinct courseid) as courseid
FROM   <table>
WHERE  branchid = 4
group by branchid

Upvotes: 0

raina77ow
raina77ow

Reputation: 106385

With my crystal ball I predict you need this instead:

SELECT COUNT(DISTINCT semesterid), 
       COUNT(DISTINCT courseid) 
  FROM Table1 
 WHERE branchid = 4;

... as the query shown should work just fine for counting the number of records (but not unique records).

Upvotes: 1

Related Questions