SQL - selecting count of different value

Question

Assume I have table count and the column is (nilai, id_courses, id_lecturer)

nilai    id_courses    id_lecturer
-----    ----------    -----------
2        1             1
2        1             1
2        1             1
3        1             1
3        1             1
1        2             1
1        2             1
5        2             1
5        2             1

then I want to create view like this :

nilai    id_courses    id_lecturer   count
-----    ----------    -----------   -----
2        1             1             3
3        1             1             2
1        2             1             2
5        2             1             2

how to do that in SQL syntax?

I just know how to count 1 value with this code

SELECT COUNT( nilai ) , id_courses, id_lecturer FROM count where nilai=1

I've read this post but its to complex, so I don't know how it's work

Are you using MySQL or MS SQL Server? (Don't tag products not involved.) — jarlh
– jarlh, Commented Feb 19, 2017 at 10:40
Tip of today: check out GROUP BY, and the aggregate function COUNT(). — jarlh
– jarlh, Commented Feb 19, 2017 at 10:41
@jarlh Im using MySQL on hostinger, thanks for the tip, and maybe that answer below is work — dondo
– dondo, Commented Feb 19, 2017 at 10:45

Fruchtzwerg · Accepted Answer · 2017-02-19 10:48:29Z

2

You need to count all distinct entries by grouping them. The query

SELECT nilai, id_courses, id_lecturer, COUNT(*) AS count
FROM count GROUP BY nilai, id_courses, id_lecturer

should exactly return the table you posted.

edited Feb 19, 2017 at 10:48

answered Feb 19, 2017 at 10:40

Fruchtzwerg

11.4k12 gold badges44 silver badges57 bronze badges

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

Collectives™ on Stack Overflow

SQL - selecting count of different value

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related