96

How do I write an SQL query to count the total number of a specific num value in the num column of a table?

Assuming we have the following data.

NAME NUM
SAM 1
BOB 1
JAKE 2
JOHN 4

Take the following query:

SELECT WHERE num = 1;

This would return these two rows.

NAME NUM
SAM 1
BOB 1
JustCarty
  • 3,839
  • 5
  • 31
  • 51
user2273278
  • 1,275
  • 2
  • 14
  • 19
  • what motivates so many duplicate answers to such a simple but ill-posed question? sigh... must avoid looking into the sausage making process! – necromancer May 23 '13 at 07:34

8 Answers8

161

Try

SELECT NAME, count(*) as NUM FROM tbl GROUP BY NAME

SQL FIDDLE

Community
  • 1
  • 1
Meherzad
  • 8,433
  • 1
  • 30
  • 40
  • 7
    If it's necessary to filter out also by specific count number, let's say get only less than 10 or more then 25, you can use `HAVING` operator, like `GROUP BY ... HAVING COUNT(*) > ...` – BotanMan Dec 08 '18 at 14:00
19

If you want to have the result for all values of NUM:

SELECT `NUM`, COUNT(*) AS `count` 
FROM yourTable
GROUP BY `NUM`

Or just for one specific:

SELECT `NUM`, COUNT(*) AS `count` 
FROM yourTable
WHERE `NUM`=1
Sirko
  • 72,589
  • 19
  • 149
  • 183
16

FOR SPECIFIC NUM:

SELECT COUNT(1) FROM YOUR_TABLE WHERE NUM = 1

FOR ALL NUM:

SELECT NUM, COUNT(1) FROM YOUR_TABLE GROUP BY NUM
Dhwani
  • 7,484
  • 17
  • 78
  • 139
13
SELECT 
   COUNT(NUM) as 'result' 
FROM 
   Table1 
GROUP BY 
   NUM 
HAVING NUM = 1
Deval Shah
  • 1,094
  • 8
  • 22
  • @necromancer please explain, why is having needed here? it makes no sense to use having in this context. If he was using it for comparing the aggregate, then it was upvote worthy. – Nick N. Jun 30 '15 at 15:17
  • 2
    @NickN. `having num = 1` is equivalent to `where num = `, so it is not wrong and it is not "makes no sense". I thought it was refreshingly different to do it this way rather than the more conventional `where`. The equivalence is not obvious at first sight, so I'd encourage trying it out yourself. – necromancer Jul 25 '15 at 07:49
  • @necromancer I still don't see it, since `where` is the same, why use `having`? – Nick N. Jul 28 '15 at 06:44
  • 6
    @NickN. `having` is for group-level criteria; `where` is for row-level criteria. They cannot be interchanged in general. In this special boundary case however, they can be interchanged. Therefore the output is the same. But, philosophically I think of `NUM` as a group-level criteria since the group by is done on `NUM` itself. Therefore I appreciate `having` instead of `where`. You will probably not appreciate my abstract concern in favor of some other concern, but just explaining it so you don't think I'm totally crazy. – necromancer Aug 12 '15 at 06:36
  • 2
    @necromancer, thanks for putting detail explanation. It makes lot of sense now. Thanks for a good discussion! – bizi Jul 31 '18 at 19:03
6

Try this Query

select NUM, count(1) as count 
from tbl 
where num = 1
group by NUM
--having count(1) (You condition)

SQL FIDDLE

Prahalad Gaggar
  • 11,389
  • 16
  • 53
  • 71
2
SELECT sum(num) WHERE num = 1;
0xAli
  • 1,059
  • 10
  • 22
  • No sir, afaik he wants the sum of the values not the number occurrence. – 0xAli May 23 '13 at 07:21
  • hey, you are right! dude there is one single correct answer, this one! – necromancer May 23 '13 at 07:27
  • 1
    @randomstring It's completely incorrect Query. **`1.`** What if OP want **`where num=2`**. **`2`** Besides this query is gonna return **`error`**, as it doesn't specifies the **`tablename`**. – Prahalad Gaggar May 23 '13 at 07:34
  • @Luv, you are right but OP didn't give a table name in the first place so i intended this as a pseudo query nothing more. – 0xAli May 23 '13 at 07:42
  • @0xAli Then too, its a wrong answer, we can't use **`sum()`** instead of **`count()`**. Please refer this **[Fiddle](http://sqlfiddle.com/#!2/5c2ff/6)** – Prahalad Gaggar May 23 '13 at 07:46
  • It's functioning as intended, `result: 2` is '1'+'1' as the two values of the returned rows not the count of them i am well aware of that, but i think that's what the OP wanted to do.. i could be wrong. but thanks for trying to correct me - always appreciated. – 0xAli May 23 '13 at 07:50
2

SELECT SUM(IF(your_column=3,1,0)) FROM your_table WHERE your_where_contion='something';

e.g. for you query:-

SELECT SUM(IF(num=1,1,0)) FROM your_table_name;

Regolith
  • 2,944
  • 9
  • 33
  • 50
user8567224
  • 21
  • 1
-3

Use this query this will give your output:

select 
  t.name
  ,( select 
       count (*) as num_value 
     from Table 
      where num =t.num) cnt 
from Table t;
nbk
  • 45,398
  • 8
  • 30
  • 47
Rohit Sahu
  • 9
  • 1