Show count value and multiple row in mysql

Question

I did try to get the answer myself by googling it but i can't find the solution.

I've two table actually.

This is suppose students table

id  | sname | cid
 1      a      1
 2      b      2
 3      c      2
 4      d      3
 5      e      4
 6      f      3
 7      g      4
 8      h      3

This is the class table

id  | sname 
 1      c1    
 2      c2    
 3      c3    
 4      c4

I want the result like below:

Result:

id  | cname | sname | count
 1      c1      a       1
 2      c2      b       2
 3      c2      c       2
 4      c3      d       3
 1      c4      e       2
 2      c3      f       3
 3      c4      g       2
 4      c3      h       3

As you can see the result will be like the above all the student name would be show alongside with class name & count would be like that.

How can I do this ?

what is id and count id of what? count of what?

CodeBird
– CodeBird

2014-03-19 10:29:25 +00:00
Commented Mar 19, 2014 at 10:29 — CodeBird
– CodeBird, Commented Mar 19, 2014 at 10:29

Raging Bull · Accepted Answer · 2014-03-19 10:53:51Z

1

Try this:

SELECT * FROM
((SELECT C.id,C.sname as cname,S.sname as sname
FROM class C INNER JOIN students S on C.id=S.cid) T1
LEFT JOIN
(Select C.id,count(S.cid) as count
FROM class C INNER JOIN students S on C.id=S.cid
GROUP BY C.id) T2 on T2.id=T1.id)
ORDER BY sname

Result:

ID  CNAME   SNAME   COUNT
1   c1      a       1
2   c2      b       2
2   c2      c       2
3   c3      d       3
4   c4      e       2
3   c3      f       3
4   c4      g       2
3   c3      h       3

See result in SQL Fiddle.

Explanation:

First part of the inner query selects id,cname and sname. Second part selects the count. And in outer query, we just join them.

edited Mar 19, 2014 at 10:53

answered Mar 19, 2014 at 10:41

Raging Bull

18.8k13 gold badges52 silver badges56 bronze badges

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

1 Comment

Adnan Shawkat Over a year ago

it works... lots of thanks .. but as my reputation is lower than 15 I can't vote your answer up... sorry for that... but still many many thanks for saving my day ...

mustaphahawi · Accepted Answer · 2014-03-19 10:38:20Z

0

 SELECT S.ID AS ID , C.SNAME AS CNAME , S.SNAME AS SNAME , CNT.COUNT AS COUNT
 FROM STUDENTS S ,  CLASS C , (
                               SELECT S.CID AS CID , COUNT(*) AS COUNT
                               FROM STUDENTS S
                               GROUP BY S.CID
                               ) AS CNT

 WHERE S.CID = C.ID AND CNT.CID = C.ID

answered Mar 19, 2014 at 10:38

mustaphahawi

2971 silver badge8 bronze badges

Comments

Andrius Naruševičius · Accepted Answer · 2014-03-19 10:47:49Z

0

A join with a subquery for count should do:

SELECT
    s.id `id`,
    c.sname `cname`,
    s.sname `sname`,
    (SELECT COUNT(1) FROM students WHERE students.cid = c.id) `count`
FROM
    students s
    LEFT JOIN class c ON s.cid = s.id

edited Mar 19, 2014 at 10:47

answered Mar 19, 2014 at 10:32

Andrius Naruševičius

8,6289 gold badges53 silver badges83 bronze badges

1 Comment

Adnan Shawkat Over a year ago

the query is not working as I've wanted ... it's only showing count 1 that's it. I need to count the class id's on student table.

Collectives™ on Stack Overflow

Show count value and multiple row in mysql

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related