Hello.
I want to do a search for expenses entered in the expenses table. I want to do in the same query: add the expenses found according to the search parameters, count how many expenses were also found according to the parameters passed and return this total, the amount and cost of each expense.
When I do not use the sum and count functions, all expenses come. But when I use any of them, comes the total or the correct amount, but only list the first expense.
I'm using cakephp 2.4 and the query is:
$despesas = $this->Despesa->query("select sum(despesas.valor_despesa) as total, count(id_despesa) as quantidade, despesas.id_despesa, despesas.despesa, despesas.valor_despesa, despesas.data_despesa, despesas.local_despesa, tipos.tipo from despesas inner join tipos on despesas.fk_id_tipo=tipos.id_tipo where year(data_despesa)='{$this->request->data['Despesa']['ano']}' and month(data_despesa)='{$this->request->data['Despesa']['mes']}'");
I've tried to do something like this:
$despesas = $this->Despesa->query("select despesas.despesa, despesas.valor_despesa, despesas.data_despesa, despesas.local_despesa, tipos.tipo from despesas inner join tipos on despesas.fk_id_tipo=tipos.id_tipo where year(data_despesa)='{$this->request->data['Despesa']['ano']}' and month(data_despesa)='{$this->request->data['Despesa']['mes']}' in (select sum(despesas.valor_despesa) as total, count(despesas.id_despesa) as quantidade)");
I can even do more than one search, ie divide this query into three 1. get the data normally; 2. pick up the total and 3. pick up the quantity. But I intend to make this experience.