CODEWITHSUNDEEP
mysqlsql
minamino
minamino

Reputation: 115

Query group by 2 column

I have 1 table with 4 columns

id, name, key, date
 1,'A'  ,'x1','2015-11-11'
 2,'A'  ,'x1','2015-11-11'
 3,'B'  ,'x2','2015-11-11'
 4,'B'  ,'x2','2015-11-11'
 5,'A'  ,'x1','2015-11-12'
 6,'A'  ,'x1','2015-11-12'
 7,'B'  ,'x2','2015-11-12'
 8,'B'  ,'x2','2015-11-12'
 9,'D'  ,'x3','2015-11-12'

I want group by [key] and [date]. Result I want is:

2015-11-11             2

2015-11-12             1

2: date 2015-11-11 have 4 rows (1,2,3,4) but duplicate key, so when group by we only have 2 row.

1: date 2015-11-12 have 5 rows (5,6,7,8,9) but have 4 rows (5,6,7,8) duplicate with date 2015-11-11, I don't want calculator => we only have 1 rows (9)

I'm sorry for my english. I hope you can understand my question.

Please help me every way. I'm using mysql.

Upvotes: 0

Views: 71

Answers (1)

jarlh
jarlh

Reputation: 44805

select key, date, (select count(*) from tablename t2
                   where t2.key = t1.key
                     and t2.date = t1.date
                     and not exists (select 1 from tablename t3
                                     where t3.key = t2.key
                                       and t3.date < t2.date))
from tablename t1

You can use a correlated sub-query to count that date's keys. Do not count if that date's key-value have already been found for an older date.

Alternative solution:

select t1.key, t1.date, count(*)
from tablename t1
  LEFT JOIN (select key, min(date) as date from tablename group by key) t2
    ON t2.key = t1.key and t2.date = t1.date
group by t1.key, t1.date

Upvotes: 1

Related Questions