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, Feb 21 '13 at 11:16
try this link http://stackoverflow.com/questions/3003457/count-vs-countcolumn-name-which-is-more-correct — Suraj Shrestha, Feb 21 '13 at 11:17

score 2 · Accepted Answer · answered Feb 21 '13 at 11:22

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 '13 at 11:22

Álvaro González

142,137
41
261
360

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 :) – Trialcoder Feb 21 '13 at 11:27

juergen d · Answer 2 · 2013-02-21T11:31:27.343

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 '13 at 11:31

answered Feb 21 '13 at 11:16

juergen d

201,996
37
293
362

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 – Trialcoder Feb 21 '13 at 11:25
sorry, I did not notice the wrong order of `where` and `group by`. Fixed now. – juergen d Feb 21 '13 at 11:31

score 0 · Answer 3 · 2013-02-21T11:25:42.880

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 '13 at 11:25

answered Feb 21 '13 at 11:16

score 0 · Answer 4 · answered Feb 21 '13 at 11:22

0

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

answered Feb 21 '13 at 11:22

bvr

4,786
1
20
24

How to count field values in mysql

4 Answers4