CODEWITHSUNDEEP
mysqlsql
Adnan Shawkat
Adnan Shawkat

Reputation: 188

Show count value and multiple row in mysql

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 ?

Upvotes: 1

Views: 80

Answers (3)

Raging Bull
Raging Bull

Reputation: 18767

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.

Upvotes: 1

Andrius Naruševičius
Andrius Naruševičius

Reputation: 8578

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

Upvotes: 0

mustaphahawi
mustaphahawi

Reputation: 297

 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

Upvotes: 0

Related Questions