Make multiple sql queries in a single query

Question

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 the cost of each expense.

When I don’t use sum and Count functions, all expenses come. But when I use any of them, comes the total or correct amount, but only lists the first expense.

I am 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:

$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. take the data normally; 2. take the total and 3. take the quantity. But I intend to do that experiment.

Answer 1

Good morning buddy, in this case I think the problem is SQL. Whenever you use aggregation functions such as SUM or COUNT SQL will return SUM or COUNT in a single row unless you group (GROUP) the results.

You would have to know exactly what your bank table looks like in order to help better, but you can see about the SQL aggregation functions in Google.

I hope I’ve helped.