This is the query that in phpmyadmin gives the right data

SELECT COUNT(message) as count, message FROM (SELECT message FROM watchdog WHERE type = 'search_success' ) as t GROUP BY message ORDER BY count DESC

search_success records the search string of successful searches. The above gives a descending list of the most popular, successful, searches. So how do I get that to work with views? It sounds like I need to add this group by module but the nearest I can get to the above in views (2) is:

SELECT COUNT(watchdog.message) AS watchdog_message 
FROM watchdog watchdog 
WHERE (watchdog.type) = ('search_success') 
GROUP BY watchdog_message 
ORDER BY watchdog_message DESC

And this gives errors:

user warning: Can't group on 'watchdog_message' query: SELECT COUNT(*) FROM (SELECT COUNT(watchdog.message) AS watchdog_message FROM watchdog watchdog WHERE (watchdog.type) = ('search_success') GROUP BY watchdog_message ORDER BY watchdog_message DESC ) count_alias in \sites\all\modules\views\includes\view.inc on line 729.

user warning: Can't group on 'watchdog_message' query: SELECT COUNT(watchdog.message) AS watchdog_message FROM watchdog watchdog WHERE (watchdog.type) = ('search_success') GROUP BY watchdog_message ORDER BY watchdog_message DESC LIMIT 0, 25 in \sites\all\modules\views\includes\view.inc on line 755.

So how do I do this?

BTW I used table wizard to add the watchdog table to views.

Comments

malc_b’s picture

Here's another example which sounds like group by should enable but I don't know how to set it up, or if it will.

I have two node type for each user generated by content_profile mode. I want to combine the data on these. So I add a view of the node tables, filter by node type a and node type b. Now I want to aggregate the data so I can have a table where the lines are:

UID, field from node type a, field from node type b

Thanks

UPDATE: Never mind about this last example as it seems like views does this anyway if I use relationships. If I have fields as above and then filter just on node type a I get just one line. If I filter by node type a or type b then I get two identical lines for each uid.

rfay’s picture

I had the same problems that you did, and it turned out that I didn't understand the "fields to group on" and "fields to aggregate with" selections. "Fields to group on" is the field that you're going to *keep* in your display. Fields to aggregate with is the field(s) that you are counting or whatever. When I set them different and understood what they wanted, I got this working just fine..

rsvelko’s picture

Title: How to use this? More examples perhaps? » How to use this? More examples perhaps? Better docs
Category: support » task
Issue tags: +Documentation

Hi, guys.

We need some minor improvements in the docs page: http://drupal.org/node/389230

1. Include the explanation from #2 above in it
2. MAke point 4. : "Click on the "SQL Aggregation: Group By Fields" and configure its settings as follows:"

to be

"Add a field named "SQL Aggregation: Group By Fields" and configure its settings as follows:"

I spent 5 mins wondering where is this SQL ... thing...

a_c_m’s picture

it turned out that I didn't understand the "fields to group on" and "fields to aggregate with" selections. "Fields to group on" is the field that you're going to *keep* in your display. Fields to aggregate with is the field(s) that you are counting or whatever.

This.

Until i read that i was going all over the place and getting VERY confused. The inline help needs to be updated to reflect the text above.

Fields to Group On: *
Select fields to group by. Attention: You need to first select these fields as Views Fields!
into
Fields to Group On: *
Select fields to group by. These are the fields that you will keep and display. Attention: You need to first select these fields as Views Fields!

Fields to Aggregate with the SQL function: *
Attention: in ANSI SQL you may not select fields that are used in grouping! If you do, Views will ignore them anyway.
into
Fields to Aggregate with the SQL function: *
Select the fields to be counted, their output will become the number of times that row is found. Attention: in ANSI SQL you may not select fields that are used in grouping! If you do, Views will ignore them anyway.

Or something like that.

I wish there was a 'high' issue state as this is a fairly major problem imho, but not critical.

rsevero’s picture

Version: 6.x-1.0-rc1 » 6.x-1.x-dev
Assigned: Unassigned » rsevero

Suggestions for handbook page from #3 already implemented.

Hope to get on the rest soon.

asb’s picture

Issue tags: +examples, +Advanced Use Cases

After trying for about an hour to understand how this module is supposed to be used, I fully second the request for more examples and documentation ;)

Interesting example use cases might be to query for term popularity based on node access stats, or to combine ratings from Voting API (which does it's own aggregation) with node creation date (querying for trends in ratings). I'm not sure if such questions can be answered with SQL grouping, so please share, if you have working examples similar to this, or any other interesting applications for SQL grouping.

If I'm getting usable results, I'll add them to the handbook page, also.

mr.ashishjain’s picture

Issue summary: View changes