0

I am using Views to get the count of my field values. I am using entity reference field in views.

There will be a lot of values, I am using the same value 2 times, which means it should show in count column count as 2. But now I am using count function using views extra handlers and raw SQL I am getting count for same id and same date in different rows it is not showing count like as 2,3 .. Any suggestions?

An example is shown in the figure below:

enter image description here

SELECT node.nid AS nid, node.created AS node_created, 'node' AS
field_data_field_vivarium_room_node_entity_type, count('node') AS
raw_sql_field
FROM 
{node} node
WHERE (( (node.status = '1') AND (node.type IN  ('vivarium_room')) ))
GROUP BY node.created
ORDER BY node_created DESC
LIMIT 10 OFFSET 0
  • 1
    A View is, essentially, a UI representation of a query. So yes, it's not only possible, but pretty much mandatory for a View to contain a query – Clive Oct 22 '15 at 11:24
  • You can use aggregation in views to simply get count function. – WaQaR Ali Oct 22 '15 at 11:31
  • look at my answer below. – WaQaR Ali Oct 22 '15 at 11:42
0

Method 1

You may use Views aggregator plus and then select group and compress on one field and count on your date field.

  • Simply enable views aggregator plus module and create your view with required fields.
  • Open format settings

Settings

  • Select Group and compress for the date field and Count for title field under group aggregation options

group aggregation

  • Its as simple as that. Here's the final output

aggregation output

Method 2

If you need more control on your view by writing your own query,
You can use View Raw SQL. It doesn't allow you to add a full sql query but you can add fields using raw sql. You would need another module Extra Views Handlers to write group by functions.

For your case,

  • Install and enable Extra Views Handlers and Views Raw SQL modules.
  • In views add Global:Query Alter field and write group by function.
  • Add another field Raw Sql, and write count(<your_date_field>). This would give you count by date.

Some referenced fields may have entity_ids instead of values. To use those fields in raw sql and query alter, look in the below screen shots how to add them.

  • Add such fields into sort criteria and you will have target_id fields in the query.

target_id fields

  • You will need to use the target_id fields in the query alter and raw sql fields for entity referenced fields

gorup by _ query alter

  • If you have fields with raw values instead of entity_ids, then you may use them straight into query alter and raw sql.

count _ raw sql

  • And the final output will look like

final output with aggregation

  • @ siddhu151dj - thanks for your response ..i am having one title field and one date fileld i need count on daily basis ..in that title field some ids will be add daily(different ids will be created on same date) ...i need in another column just count in view is it possible with above modules ..i have posted pic in above.. – DINE Oct 26 '15 at 4:48
  • @ siddhu151dj - i am trying to insert my query in "query alter" i am confusing to insert my query in that ... can you suggest on this query SELECT er.field_vivarium_room_value AS vivarium_room, COUNT( field_vivarium_room_value ) AS cage_count FROM mcms_field_revision_field_date_of_cage_card_creation re, mcms_field_data_field_localityroom ms, mcms_field_data_field_vivarium_room er WHERE re.entity_id = ms.entity_id AND ms.field_localityroom_target_id = er.entity_id GROUP BY er.field_vivarium_room_value ORDER BY re.field_date_of_cage_card_creation_value DESC LIMIT 0 , 30 – DINE Oct 26 '15 at 6:18
  • If not done, first enable 'Show the SQL query' in views settings to display the query for your view. Then copy your date field shown in the query and add it under group by function in your query alter field. Then using raw sql field, write count function on your title field to display the count. If you need in more detailed, let me know I will modify it in the answer. – siddhu151dj Oct 26 '15 at 13:38
  • Post your query of your view here, Ill update the answer. Also specify which is date field and title field. – siddhu151dj Oct 26 '15 at 13:40
  • @ siddhu151dj - In extra views handler there is a lot of fields to enter the query.i should change only group by as date? – DINE Oct 27 '15 at 7:00
1

Solution 1: To perform simple count function you may set Use Aggregation:yes in views under Advanced tab as shown in figure below;

enter image description here

Solution 2: If you want more complex functions the you may use Views Calc.

Views Calc:

This module adds simple calculations to a views table. Requires the Views module.

You can SUM, AVERAGE, COUNT, MIN, MAX, or AVG selected columns. A plug in theme makes the view look more spreadsheet-like (right justifies numeric fields, shades calculated columns and rows, and underlines calculated rows).

You can also create custom dynamic fields that can be used in any view using SQL snippets like CONCAT('field_first_name', ' ', 'field_last_name') or DATE_FORMAT(node.created, '%m/%%d/%Y'). The custom fields are set up in admin/settings/views_calc.

  • @ WaQaR Ali - i have reffered views calc module with this is it possible count by date --i mean for todays date i have added flowers 2 it should show flowers 2 and for tomorrows date again i need count should altlest minium for 3 days count should display on view by date is it possible? – DINE Oct 22 '15 at 12:02
  • it is not a solution how we add aggregator on a specific date Questioner add count on specific report not for all – Adi Oct 22 '15 at 12:04
  • @DINE add global php field in this add your code or write custom query against your report after add php count function to get total record against your report then print it – Adi Oct 22 '15 at 12:08
1

there is hook_views_query_alter which

Alter the query before executing the query.

see how to use hook_views_query_alter() to modify where condition?

0

The key challenge you'll face here is that neither Views nor Date modules provide aggregation options for date-based fields.

This can be solved by using the Views Date Format SQL module. It adds a nice little check-box that allows you to group dates by a custom formatted string, as shown in this example also:

enter image description here

In the above example, the custom date format is set to "M Y" (which translates to Oct 2015 for example), and the box for "Use SQL to format Date" was checked. This is the only configuration needed.

  • @ Pierre.Vriens - thanks for your response..i will check with this and come back to you. – DINE Oct 26 '15 at 5:36
  • @ Pierre.Vriens - i have installed the module,how can i configure this ... – DINE Oct 26 '15 at 5:43
  • I enhanced my answer to explain the configuration part ... – Pierre.Vriens Oct 26 '15 at 8:02
  • @ Pierre.Vriens - i tried with this but it is showing all records on that date for the field.i need just count under that date...like i have 5 ids means it should show just 5 but this format is showing all of my 5 records.. – DINE Oct 26 '15 at 8:15
  • @ Pierre.Vriens - Your suggestion helped me a lot ...i need count for each id(field) on that date i have posted scrn shot above..any suggestions – DINE Oct 26 '15 at 8:30

Not the answer you're looking for? Browse other questions tagged or ask your own question.