CODEWITHSUNDEEP
sqlsql-servergroup-by
fishmong3r
fishmong3r

Reputation: 1434

Using multiple group by with count

I have this table:

+----+-------+------------+
| id |  name |       date |
+----+-------+------------+
| 1  | name1 | 01/01/2015 |
+----+-------+------------+
| 2  | name1 | 01/01/2015 |
+----+-------+------------+
| 3  | name1 | 01/02/2015 |
+----+-------+------------+
| 4  | name2 | 02/14/2015 |
+----+-------+------------+
| 5  | name2 | 02/16/2015 |
+----+-------+------------+
| 6  | name3 | 02/19/2015 |
+----+-------+------------+

And I need my query to return this:

+-------+---+
| name1 | 2 |
+-------+---+
| name2 | 2 |
+-------+---+

So basically I need the name and the count of occurences, but 1 day only counts once. Also I need names only if the count based on the previous criterion is above 1. Tried like this but the result is not what I expect. I mean I checked the numbers 'manually' and 

Select
  name, count(id)
From
  table
Group By
  name, date
Having
  Count(id) > 1

Upvotes: 1

Views: 53

Answers (1)

jarlh
jarlh

Reputation: 44696

Select
  name, count(distinct date)
From
  table
Group By
  name
Having
  count(distinct date) > 1

Upvotes: 4

Related Questions