CODEWITHSUNDEEP
mysqlsqlcount
Trialcoder
Trialcoder

Reputation: 6014

How to count field values in mysql

My table -

pid     id  subject section
1        1  7            0
2        1  12           0
3        1  13           1
4        1  13           2
5        1  13           3
6        1  14           3
7        1  14           4
8        2  15           1
9        2  16           1

Result to be obtained - 

id subject total
1    7      1
1    12     1
1    13     3
1    14     2

My Query -

SELECT DISTINCT(SUBJECT), count(section)
FROM mytable
GROUP BY section
WHERE id = 1

But this is throwing an error, let me know what I am doing wrong

Error

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE id = 1' at line 4: SELECT DISTINCT(SUBJECT), count(section) FROM mytable GROUP BY section WHERE id = 1

Upvotes: 0

Views: 7623

Answers (4)

juergen d
juergen d

Reputation: 204914

You need to group by subject since this is the distinct value. In that group you want to count the appearances of section.

SELECT 1 as id, subject, count(section) as total
FROM mytable
WHERE id = 1
GROUP BY subject

And since the id column is not in the group, you can select that static value or you could replace it with an aggregate function like min(id) as id instead of 1 as id

Upvotes: 1

user1919238
user1919238

Reputation:

You want to group by subject rather than section. And you don't need distinct--it is redundant with GROUP BY.

SELECT id, subject, count(section)
   FROM mytable
   WHERE id = 1
   GROUP BY id, subject

Upvotes: 0

bvr
bvr

Reputation: 4826

SELECT SUBJECT, count(section)
FROM mytable
WHERE id = 1
GROUP BY SUBJECT

Upvotes: 0

Álvaro González
Álvaro González

Reputation: 146630

The syntax error is due to a misplaced WHERE clause. It needs to go before GROUP BY. Additionally:

  • You are grouping by the column you want to sum.
  • You are using DISTINCT unnecessarily.

Query should be on this line:

SELECT SUBJECT, count(section)
FROM mytable
WHERE id = 1
GROUP BY SUBJECT

Upvotes: 2

Related Questions