get result filtered by count grouped by date in cakephp 3
Date : March 29 2020, 07:55 AM
I hope this helps you . You're nearly there, you need to use a HAVING clause, WHERE won't work here, as it works on single results, rather than on aggregated ones, which is what you need here. SQL functions can be built using the function builder, and in order to create a comparison with a function expression, you'll have to leavarege the expression builder, using for example the lt() (less than) method. $this
->find()
->select(['CustomApi.id', 'CustomApi.api_key'])
->leftJoinWith('ApiCalls', function ($q) {
return $q->where(['ApiCalls.created' => date('Y-m-d')]);
})
->group('CustomApi.id')
->having(function ($exp, $q) {
return $exp->lt(
$q->func()->count('ApiCalls.id'),
100
);
});
|
SQL - How can I sum up a column after the results have been grouped and filtered in the having clause?
Date : March 29 2020, 07:55 AM
will be helpful for those in need Here is my current query: The objective is to find accounts that have received at least $500 in deposits within 30 days of their first deposit. Some accounts have been closed and re-opened, hence the first line of the 'WHERE' clause. , You can try something like that: select filtered.accountNumber,
min(filtered.transDate) as "first deposit",
filtered.transDate,
CAST(DATEADD(d,30,min(filtered.transDate)) as date) as "30 days",
sum(filtered.amount) as "sum",
filtered.amount,
filtered.accountOpenDate
from
(
select * from Deposits
inner join Members on Deposits.accountNumber = members.accountNumber
where Deposits.transDate >= members.accountOpenDate
and Deposits.accountNumber = 123456
having Deposits.transDate between min(Deposits.transDate) and DATEADD('d',30,min(Deposits.transDate))
) as filtered
group by filtered.accountNumber
having sum(filtered.amount) >= 500
|
Sums of points per date grouped by additional column in a form of a list
Date : March 29 2020, 07:55 AM
Hope that helps I would like to obtain sum of points of some technologies per date from pandas data frame. A reproducible example: , We can convert your original data to this format s=data.tech.str.len()
newdf=pd.DataFrame({'date':data.date.repeat(s),'score':data.score.repeat(s),'tech':np.concatenate(data.tech.values)})
newdf
Out[477]:
date score tech
0 2017-01-31 1 c++
0 2017-01-31 1 python
1 2017-02-28 4 c++
1 2017-02-28 4 c
1 2017-02-28 4 java
2 2017-02-28 2 java
pd.pivot_table(newdf,index='date',columns='tech',values='score',aggfunc='sum',fill_value=0)
Out[476]:
tech c c++ java python
date
2017-01-31 0 1 0 1
2017-02-28 4 4 6 0
|
Subtraction SUMs of values in first column (Amount) grouped by another second column (Products) with condition in anothe
Date : March 29 2020, 07:55 AM
This might help you Because some Product may be or may not in some Process, first we must collect all the valid Products and only then calculate the result Here the code I've just tested on your data: with all_prod as (select distinct Product from t1)
select
ap.Product, coalesce(B.Amount,0) - coalesce(A.Amount,0) Amount
from all_prod ap
left join (
select SUM(Amount) Amount, Product, 2 Process
from t1
where Process = 2
group by Product, Process) A on A.Product = ap.Product
left join (
select SUM(Amount) Amount, Product, 2 Process
from t1
where Process = 1
group by Product, Process) B on ap.Product = B.Product
|
Cumulative sum on different columns grouped by date and filtered differently
Date : March 29 2020, 07:55 AM
I wish this help you This is a classic example of a Cumulative Sum DAX Pattern. You do not need separate tables. a_cum =
VAR CurrentID = [id]
RETURN
CALCULATE (
SUM ( Table01[a] ),
FILTER (
ALLEXCEPT ( Table01, Table01[dte] ),
Table01[id] <= CurrentID
)
)
b_cum =
VAR CurrentID = [id]
RETURN
CALCULATE (
SUM ( Table01[b] ),
FILTER (
ALLEXCEPT ( Table01, Table01[dte] ),
Table01[id] >= CurrentID
)
)
|
Boards Message :
Related QUESTIONS :