How to count field values in mysql

Question

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

If you get an error message, please post it together with your questions. Error messages have a purpose: help diagnose issues. Don't make us guess. — Álvaro González
– Álvaro González, Commented Feb 21, 2013 at 11:16

Álvaro González · Accepted Answer · 2013-02-21 11:22:36Z

2

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

answered Feb 21, 2013 at 11:22

Álvaro González

147k45 gold badges282 silver badges378 bronze badges

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

1 Comment

Trialcoder Over a year ago

Perfect..working like a charm...thx a lot..and thanks for the comments..i am on learning mode..so it will really going to help me alot :)

juergen d · Accepted Answer · 2013-02-21 11:31:27Z

1

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

edited Feb 21, 2013 at 11:31

answered Feb 21, 2013 at 11:16

juergen d

205k40 gold badges303 silver badges377 bronze badges

2 Comments

Trialcoder Over a year ago

It throws an 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 1 as id, subject, count(section) as total FROM mytable GROUP BY subject WHERE id = 1

juergen d Over a year ago

sorry, I did not notice the wrong order of where and group by. Fixed now.

bvr · Accepted Answer · 2013-02-21 11:22:56Z

0

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

answered Feb 21, 2013 at 11:22

bvr

4,8361 gold badge23 silver badges24 bronze badges

Comments

score 0 · Accepted Answer · 2013-02-21 11:25:42Z

0

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

edited Feb 21, 2013 at 11:25

answered Feb 21, 2013 at 11:16

user1919238

Collectives™ on Stack Overflow

How to count field values in mysql

4 Answers 4

1 Comment

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related