## Mysql Sum of Two aggregate functions Not Working

Hi I want to add the result of two aggregate functions, but I'm getting "Invalid Use of Group Function". Can anyone correct the following query :

SELECT mc.complaint_type_id, mc.complaint_type, sum(sum(case when c.is_solved = 1 then 1 else 0 end) + sum(case when ((c.is_solved = 0) and (c.res_user_id is null)) then 1 else 0 end)) as complaints_count, from svk_apt_master_complaints mc left join svk_apt_complaints c on c.complaint_type_id = mc.complaint_type_id and c.is_active = 1 and c.customer_id = 1 and c.association_id = 1 group by mc.complaint_type_id

Try this:

SELECT mc.complaint_type_id, mc.complaint_type, sum(case when c.is_solved = 1 then 1 else 0 end) + sum(case when ((c.is_solved = 0) and (c.res_user_id is null)) then 1 else 0 end) as complaints_count from svk_apt_master_complaints mc left join svk_apt_complaints c on c.complaint_type_id = mc.complaint_type_id where c.is_active = 1 and c.customer_id = 1 and c.association_id = 1 group by mc.complaint_type_id, mc.complaint_type

You don't need `sum()`

when you use `+`

operator. Also, `SUM`

is aggregate function.

Also, you select columns, that are not included in aggregate: `mc.complaint_type`

. You need to whether include it in `group by`

or just remove it.

**Data Management with SQL for Ecologists: SQL Aggregation and ,** , for example: MAX , MIN , and AVG . MySQL supports all the five (5) ISO standard aggregate functions COUNT, SUM, AVG, MIN and MAX. SUM and AVG functions only work on numeric data. If you want to exclude duplicate values from the aggregate function results, use the DISTINCT keyword. The ALL keyword includes even duplicates. If nothing is specified the ALL is assumed as the default.

You need to specify mc.complaint_type column in group by

SELECT mc.complaint_type_id, mc.complaint_type, sum(case when c.is_solved = 1 then 1 else 0 end) + sum(case when c.is_solved = 0 and c.res_user_id is null then 1 else 0 end) as complaints_count, from svk_apt_master_complaints mc left join svk_apt_complaints c on c.complaint_type_id = mc.complaint_type_id and c.is_active = 1 and c.customer_id = 1 and c.association_id = 1 group by mc.complaint_type_id,mc.complaint_type

**Multiple aggregate functions in one SQL query from the same table ,** pulling data from the same table, but with different filter conditions. The SUM() and AVG() functions return a DECIMAL value for exact-value arguments (integer or DECIMAL), and a DOUBLE value for approximate-value arguments (FLOAT or DOUBLE). The SUM() and AVG() aggregate functions do not work with temporal values. (They convert the values to numbers, losing everything after the first nonnumeric character.)

Remove nested `sums`

and add `mc.complaint_type`

to `GROUP BY`

clause. If you need to add values of two aggregate functions use `+`

operator, not an aggregate function.

SELECT mc.complaint_type_id, mc.complaint_type, sum(case when c.is_solved = 1 then 1 else 0 end) + sum(case when c.is_solved = 0 and c.res_user_id is null then 1 else 0 end) as complaints_count, FROM svk_apt_master_complaints mc LEFT JOIN svk_apt_complaints c ON c.complaint_type_id = mc.complaint_type_id and c.is_active = 1 and c.customer_id = 1 and c.association_id = 1 GROUP BY mc.complaint_type_id, mc.complaint_type

I've also reformatted your code and removed unnecessary parentheses.

**MySQL SUM - Calculating The Sum of Values,** () function calculates the total of 1, 1, 2, and 3. Find answers to SQL aggregate function SUM not working from the expert by patient X and the sum of all payments. If two visits, first one $80 and second one $100

**How do use an aggregate function in a WHERE clause in an SQL ,** Can we use aggregate function in where clause in MySQL? The SUM () function is an aggregate function that allows you to calculate the sum of values in a set. The syntax of the SUM () function is as follows: SUM ( DISTINCT expression) Here is how the SUM () function works: If you use the SUM () function in a SELECT statement that returns no row, the SUM () function returns NULL, not zero.

**12.20.1 Aggregate (GROUP BY) Function Descriptions,** The SUM() and AVG() aggregate functions do not work with temporal values. Multiple transactions may be occurring at the same time, each of which may Simplest solution works in all databases: Filtered aggregate functions (or manual pivot) This solution allows for calculating all results in a single query by using 8 different, explicit, filtered aggregate functions and no GROUP BY clause (none in this example. More complex cases where GROUP BY persists are sill imaginable).

**Aggregate Functions part 2: SUM() and AVG() in MySQL with ,** In Aggregate Functions Part 1: COUNT() — With examples in MySQL, we on aggregate functions, this blog post will look at 2 more functions: SUM() and AVG() . Working Examples. The WHERE clause does not perform this type of filtering: Next up, I will explore two other powerful functions from the aggregate family: In powerbuilder 9 User variables does not work with aggregate functions: [mysqld-5.0.67-community-nt]FUNCTION montacargas.sum does not exist Code :