Grouping field and Group By

You're absolutely right, it is not clear what I have written. So I try it again. I think an example will clarify what I mean.

We have the entity (table) orders:

If you use the group by feature you can sum (or use other aggregation functions) to generate some aggregated fields. If we use the sum function over the OrderTotalAmount we get a single field with the value of 1'281.00. Its equals more or less the SQL Statement:

SELECT sum(Order Total Amount) FROM orders;

I know the views module joins some fields into it, but for describing my problem, we can abstract from this fact.

The shown aggregation is ok when you look for the whole sum. But normally you intent to do something like:

SELECT sum(OrderTotalAmount), OrderDate FROM orders GROUP BY OrderDate;

If we assume, we can specify the granularity (month) of the OrderDate we get something like this:

Is it possible to do such aggregations with Views? I hope the question is now more clear and understandable.

Sorry when I reopen this issue. I check it out in more detail. The problem is the views module itself. The date is stored as a UNIX timestamp. Therefor the core views handlers are used (filter, field and sort uses all views_handler_sort_date, views_handler_filter_date, views_handler_field_date respectively).

I change the issue from support request to feature request, because this feature is missing in the views core. I would like to provide a patch for this, but it is not that easy.

There seems no easy way to determine if a field is going to be added as "group by". The group by is added in plugins/views_plugin_query_default.inc on line 1200. If a field is added as group by is determine on line 1081. If we can determine if a field is going to be used in group by, then we could change the formatting. This could be then a part of handlers/views_handler_field_date.inc.

Can some one put me in the right direction?

FYI: This issue seems to be related to this one:
#1159786: group by time ranges: years, quarters, months, weeks, days, hours, etc.

Thank you. My problem is how to build such a query with views. How it needs to look like is clear.

Since you have normally UNIX timestamps for dates you need to do it like this:

SELECT DATE_FORMAT(FROM_UNIXTIME(postdate), '%Y-%m-%d') AS dd, COUNT(id) FROM MyTable GROUP BY dd;

or this would also work:

SELECT postdate AS dd, COUNT(id) FROM MyTable GROUP BY DATE_FORMAT(FROM_UNIXTIME(dd), '%Y-%m-%d');

We have two problems here:

1. Group by with date is database vendor specific. For MySQL the above solutions are valid, for different vendors (such as PostgreSQL) its different.)
2. The code of views isn't that flexible to add a database vendor specific addition for group by statements. There is no hook or API for such a thing.

I see one simple solution to this problem:
In the date field handler we add the field twice, once we use it for formatting and once we use it for internal use (such as group by). The formatting can be controlled fully by the field handler, so we could simply take the second field. For default we use the database formatted version.

The query would look like this:

SELECT DATE_FORMAT(FROM_UNIXTIME(postdate), '%Y-%m-%d') AS postdate_default, postdate AS postdate_second, COUNT(id) FROM MyTable GROUP BY postdate_default;

Has the solution described above a chance to be committed into the views core? There is some sort of redundancy in it.

I'm not sure which file / class you mean. I found in the date module the file:
date/date_api/date_api_sql.inc

There is all the code required to implement this for specific database vendor. (Convert UNIX timestamp to date, format it and build the granularity form.)

But then the views module is depending on Date. Should we move this functionally into a separate module to avoid new dependencies for Views?

Proposed Approach: We could override all the date fields with a custom date field handler. This new handler then adds the required code. Perhaps we need some changes in the core to allow insertion of this new code.