WHERE BETWEEN Clause and Aggregates

I can offer some help. The answer to 1) is to make a view.

create view 'aggregated-data' as
SELECT
retailer_id, vendor_code, company_name,
SUM(num_sales) AS total_trans_count,
SUM(revenue) AS total_revenue
FROM vrpt_rev_daily_by_retailer_v1
GROUP BY retailer_id`

I’m not sure what to tell you about the second part about the where clause. But I think there’s a solution. I’ll check.

Hi @charlesgodwin – many thanks for your response, however I don’t think that will work because I need the result of the query to be the aggregation based on the date range – so sum of revenue by retailer between any two given dates. This can only be achieved with the WHERE being specified before the GROUP BY.

Creating the above new view will simply give the aggregate value across the entire row set since day one.

That said, breaking down the original view might be the answer somewhere along the line.

I’ve looked at the Search Builder feature which may provide an answer, but there are a lot of rows, so a server-side solution is required really.

select retailer_id, vendor_code, company_name,total_trans_count,total_revenue from view_name where ...... is valid syntax. I think there’s a way to do it in WPDA but I didn’t find it yet. hopefully someone can help with that part.

The correcte code for a view you can use is

create view 'aggregated-data' as
SELECT
retailer_id, vendor_code, company_name,
SUM(num_sales) AS total_trans_count,
SUM(revenue) AS total_revenue,
trans_date
FROM vrpt_rev_daily_by_retailer_v1
GROUP BY retailer_id`

Hi Matt,

What is the status of this topic? Have you been abel to fix this with a view as Charles proposed? Did you check out the Search Builder? Loading all rows into the Search Builder might not be an issue in your case as you only need to load the aggregated data, not the entire data set.

Does this help?
Peter

Hi Peter / Charles

Apologies for the late reply – I’m back on this today. No – Charles’ proposal didn’t / won’t work for the reasons I previously outlined. The date range criteria needs to be applied server-side before the aggregation summing is done e.g.

SELECT
retailer_id, vendor_code, company_name,
SUM(num_sales) AS total_trans_count,
SUM(revenue) AS total_revenue
FROM vrpt_rev_daily_by_retailer_v1
WHERE trans_date BETWEEN <<DATE 1>> AND <<DATE 2>>
GROUP BY retailer_id

As such, the Search Builder doesn’t work either. Essentially what’s required is the ability to build an aggregate query dynamically then submit to the server to get the result set.

WPDA seems to be very much a client-side-centric tool so you can only work with the data once it’s been returned to the browser. In theory this should be a really simple requirement in order to produce reports which are more often than not based on date-ranged grouping of data.

Am I missing something fundamental here with WPDA?

Thanks,

Matt

Hi Matt,

It looks to me like the Search Builder is the right tool to handle your query. Just to be sure we’re talking about the same Search Builder, I don’t mean the Query Builder. The Search Builder is a plugin for the Data Publisher that allows to interactively build queries. Here is a demo:
https://wpdataaccess.com/search-builder-demo/

On the demo page:
– click on “ADD CONDITION”
– select column “Date Of Birth”
– select condition “Between”
– enter from: 1998-11-01
– enter until: 1998-11-30

Notice that the query returns only rows which meet the condition. Isn’t this what you are looking for?

There is also a row grouping plugin for the Data Publisher, see demo:
https://wpdataaccess.com/row-grouping-client-side/

And you can combine the search builder and row grouping. The only limitation is that you need to load your whole dataset on page load. If that is an issue, you could add a search form before the publication which makes a pre selection.

Please let me know if this works for you,
Peter