Question: I need to create a view that would display a table that would show a count of items of content created on a particular day, like this:

```
Date       Items
2014-2-1   2
2014-2-2   3
2014-2-3   7

```

I created a view with aggregation, added a the following fields:

* "post date" with custom format Y-m-d and "Strip HTML tags" and "Remove whitespace" enabled in Rewrite Results section.
* "nid" with Aggregation type = Count

then I added a Sort Criteria on Post date with granularity set to "Day" and aggregation type to "Group results together." Unfortunately, the results that I get are:

```
Date      Items
2014-2-1  1
2014-2-1  1
2014-2-2  1
2014-2-2  1
2014-2-2  1

```

What did I miss?

Answer: 
The problem is the query your view is running actually looks like this:

```
SELECT node.created AS node_created, COUNT(node.nid) AS nid, DATE_FORMAT((DATE_ADD('19700101', INTERVAL node.created SECOND) + INTERVAL 0 SECOND), '%Y%m%d') AS node_created_day
FROM 
{node} node
WHERE (( (node.status = '1') AND (node.type IN  ('page')) ))
GROUP BY node_created, node_created_day
ORDER BY node_created_day DESC
LIMIT 10 OFFSET 0

```

It is grouping by the node\_created field which is a timestamp.

You have 2 options:

1) Install the [Views Date Format SQL](https://drupal.org/project/views_date_format_sql) module

2) Apply [this patch](https://drupal.org/node/844202#comment-5294936)

Both methods change the query so the date is actually formatted with MySQL, and then this value is used for the grouping.

The Views Date Format SQL module would change the query to look like this:

```
SELECT COUNT(node.nid) AS nid, DATE_FORMAT((DATE_ADD('19700101', INTERVAL node.created SECOND) + INTERVAL 0 SECOND), '%Y-%m-%d') AS node_created, DATE_FORMAT((DATE_ADD('19700101', INTERVAL node.created SECOND) + INTERVAL 0 SECOND), '%Y%m%d') AS node_created_day
FROM 
node node
WHERE (( (node.status = '1') AND (node.type IN  ('page')) ))
GROUP BY node_created, node_created_day
ORDER BY node_created_day DESC
LIMIT 10 OFFSET 0

```

This has the same effect as Mike's solution - *but* requires another module, so I would go with Mikes answer on this one...
Update: this used to work for the version I had. However it appears this isn't the case for some people so I'll leave it in case it is helpful to someone, but the other answers are probably better.

If you add the 'Minimum' aggregate function to your 'post date' ensuring you keep the granularity settings on your sort criteria, you'll get the results you want.

Adding the 'Minimum' aggregate function alters how the generated SQL query groups the results. Here is the before and after:

```
SELECT node.created AS node_created, COUNT(node.nid) AS nid, DATE_FORMAT((DATE_ADD('19700101', INTERVAL node.created SECOND) + INTERVAL 0   SECOND), '%Y%m%d') AS node_created_day
FROM 
node node
WHERE (( (node.status = '1') AND (node.type IN  ('dummyNode')) ))
GROUP BY node_created, node_created_day
ORDER BY node_created_day DESC
LIMIT 10 OFFSET 0

vs

SELECT COUNT(node.nid) AS nid, MIN(node.created) AS node_created, DATE_FORMAT((DATE_ADD('19700101', INTERVAL node.created SECOND) + INTERVAL 0  SECOND), '%Y%m%d') AS node_created_day
FROM 
node node
WHERE (( (node.status = '1') AND (node.type IN  ('dummyNode')) ))
GROUP BY node_created_day
ORDER BY node_created_day DESC
LIMIT 10 OFFSET 0

```

You can see the 'GROUP BY node\_created' is removed.