How to count distinct group by field SQL

Question

I have this data in my database :

nama             tanggal     
ADIESTY.SW       2017-03-01  
ADIESTY.SW       2017-03-01  
ADIESTY.SW       2017-03-02  
ADIESTY.SW       2017-03-02  
ADIESTY.SW       2017-03-03  
ADIESTY.SW       2017-03-03  
ADIESTY.SW       2017-03-05  
ADIESTY.SW       2017-03-05  
ALFRED APDIAN    2017-03-01  
ALFRED APDIAN    2017-03-01  
ALFRED APDIAN    2017-03-02  
ALFRED APDIAN    2017-03-02  
ARI PUTRA        2017-03-01  
ARI PUTRA        2017-03-01  
ARI PUTRA        2017-03-02  
ARI PUTRA        2017-03-02  
ARI PUTRA        2017-03-03  
ARI PUTRA        2017-03-03

How do I count by distinct date (tanggal)? So the result I want is:

    nama         tanggal
ADIESTY.SW        4
ALFRED APDIAN     2
ARI PUTRA         3

The double quotes aren't actually there in your data and expected output, are they? — Gurwinder Singh, Mar 03 '17 at 08:26
It's simple aggregation - `select name, count(*) from t group by name` — Gurwinder Singh, Mar 03 '17 at 08:28
@AkbarTriNanda - I changed your question to be specific to what you wanted. In future, please give exact data and sample output based on that. Also show your effort/problem which you are not able to resolve by yourself. Cheers! — Utsav, Mar 03 '17 at 08:51
Possible duplicate of [MySQL Query GROUP BY day / month / year](http://stackoverflow.com/questions/508791/mysql-query-group-by-day-month-year) — Shadow, Mar 04 '17 at 01:42

Utsav · Accepted Answer · 2017-03-03T08:52:07.313

10

Use this

select nama,count(distinct tanggal) from your_table
group by nama;

http://rextester.com/ZOZQK78377

edited Mar 03 '17 at 08:52

answered Mar 03 '17 at 08:46

Utsav

7,914
2
17
38

score 1 · Answer 2 · answered Mar 03 '17 at 08:37

1

When you post the question specify the type of database used (Oracle,MySQL,..)

SELECT name, COUNT(*)
FROM yourTable
GROUP BY name

answered Mar 03 '17 at 08:37

Ahmed

76
8

score 0 · Answer 3 · answered Mar 03 '17 at 08:29

0

To get that data the query should be

SELECT `name`, COUNT(*) as `count`
FROM `table`
GROUP BY `name`

answered Mar 03 '17 at 08:29

user1898027

330
2
13

score -1 · Answer 4 · answered Mar 03 '17 at 08:31

Try this :

         select name,count(name) as count from (
  select 'ADIESTY.SW' as Name,'2017-03-01' as date union
  select 'ADIESTY.SW' as Name,'2017-03-02'as date union
  select 'ADIESTY.SW' as Name,'2017-03-03'as date union
  select 'ADIESTY.SW' as Name,'2017-03-05'as date union
  select 'ALFRED APDIAN'as Name,'2017-03-01'as date union
  select 'ALFRED APDIAN' as Name,'2017-03-02'as date union
  select 'ARI PUTRA' as Name, '2017-03-01'as date union
  select 'ARI PUTRA'  as Name,'2017-03-02'as date union
  select 'ARI PUTRA'  as Name, '2017-03-03'as date
  )t
  group by name

How to count distinct group by field SQL

4 Answers4