How to Combine Aggregate Functions in MySQL

How to combine aggregate functions in MySQL?

You have to use subqueries:

  SELECT x.user, 
         AVG(x.cnt)
    FROM (SELECT user, COUNT(answer) AS cnt
            FROM surveyValues 
           WHERE study='a1' 
        GROUP BY user) x
GROUP BY x.user

You can't wrap an aggregate with another aggregate. You could wrap an analytic in an aggregate, if MySQL supported analytic/ranking/windowing functions...

Is there a way to combine two aggregate functions in MySQL to get distinct values?

SELECT  *
FROM    customers c
JOIN    stores s
ON      s.postcode =
        (
        SELECT  postcode
        FROM    orders o
        JOIN    order_contents oc
        USING   (order_id)
        WHERE   o.mobile_number = c.mobile_number
        GROUP BY
                postcode
        ORDER BY
                SUM(quantity) DESC
        LIMIT 1
        )

This won't show customers who have made no orders at all. If you need those, change the JOIN to stores to a LEFT JOIN

How to combine aggregate with nonaggregated in mysql query

When MySQL only_full_group_by mode is on, it means this means that if you GROUP BY by some column, then you can only select one of two things , the column you group by and an aggregate function like MAX(), MIN()...;
If you do not want to change your sql_mode (that may result in problems in the future ),
below query should work for you.

    SELECT max(name) as name,
           max(team) as team,
           max(timestamp)  as timestamp
FROM competitors join location using(competitor_id)
where competitor_id in ( 
select distinct competitor_id from  competitors ) 
group by competitor_id
order by timestamp DESC ;

http://www.sqlfiddle.com/#!9/2cc8a6/1

Refrence link: SELECT list is not in GROUP BY clause and contains nonaggregated column .... incompatible with sql_mode=only_full_group_by

How to combine aggregate function Sum() and Max()

I see that you are trying to get sum of all orders and not individual orders.
In that case why do you need order ID and date in the final output?

The following query is what you are asking for.

    SELECT (select max(ID) from ORDERS) as ID,
       (select max(DATE) from ORDERS) as DATE,
       SUM(TOTAL_VALUE),
       SUM(QUANTITY)
FROM (
SELECT distinct O.ID
              , O.DATE
              , max (O.TOTAL_VALUE) TOTAL_VALUE
              , SUM (OI.QUANTITY ) over(partition by O.ID) QUANTITY
FROM ORDERS O
INNER JOIN ORDERS_ITEMS OI ON O.ID = OI.ID
WHERE O.DATE >= (GetDate() - 90) -- SELECT last 90 days
group by O.ID,OI.QUANTITY,O.DATE
)A

MySQL: In a select with aggregate functions, how to join two columns not in aggregate functions?

The operator || is the Logical OR operator in MySql (deprecated since version 8.0.17) and not the concatenation operator.

So when you use it with strings as operands MySql does an implicit conversion of the strings to numbers (check Type Conversion in Expression Evaluation for details) which has 0 as a result for any string that does not start with a number and the final result is 0 (= false).

The result could also be 1 (= true) if any string starts with a non zero numeric part, just like (I suspect) is the case with the column CustZipCode and you get 1 for the "Address".

If you want to concatenate strings you should use the function CONCAT():

select CONCAT(customers.CustFirstName, customers.CustLastName) as "Name", 
       CONCAT(customers.CustStreetAddress, customers.CustZipCode, customers.CustState) as "Address",
       ......................................

MySQL Aggregate Function with group by and join

You are, I believe, seeking two different summaries of your data. One is a sum of salaries by category, and the other is a count of employees, also by category.

You need to use, and then join, separate aggregate queries to get this.

SELECT a.category, a.amount, b.cnt
  FROM (
         SELECT e.category, SUM(p.amount) amount
           FROM employees e
           JOIN payroll_histories p ON e.id = p.employee_id
          GROUP BY e.category
       ) a
   JOIN (
          SELECT category, COUNT(*) cnt
            FROM employees
           GROUP BY category
        ) b ON a.category = b.category

The general principle here is to avoid trying to use just one aggregate query to aggregate more than one kind of detail entity. Your amount aggregates payroll totals, whereas your count aggregates employees.

Alternatively for your specific case, this query will also work. But it doesn't generalize well or necessary perform well.

 SELECT e.category, SUM(p.amount) amount, COUNT(DISTINCT e.id) cnt
   FROM employees e
   JOIN payroll_histories p ON e.id = p.employee_id
  GROUP BY e.category

The COUNT(DISTINCT....) will fix the combinatorial explosion that comes from the join.

(Pro tip: use the explicit join rather than the outmoded table,table WHERE form of the join. It's easier to read.)

Multiple aggregate functions in MySQL with different conditions

Your current code just lacks a relation between the outer query and the subqueries. In theory, you just need to correlate the queries:

SELECT t2.lender_name, COUNT(t1.id) as total,
SUM(t1.submit_date IS NULL) AS num_incomplete,
 (SELECT AVG(DATEDIFF(due_date,now())) 
  FROM table_1 t3
  WHERE submit_date IS NULL
    AND t3.lender_name = t2.lender_name) as avg_incomplete_due_in,
 (SELECT AVG(DATEDIFF(due_date,submit_date))
  FROM table_1
  WHERE submit_date IS NOT NULL
    AND t3.lender_name = t2.lender_name) as avg_complete_turnaround
FROM table_1 t1
INNER JOIN table_2 t2 ON t2.fid = t1.id
WHERE t1.due_date <= '2010-12-31'
GROUP BY t2.lender_name

In practice, the query is not very efficient in MySQL. You can rewrite it in the following way:

SELECT
  t2.lender_name,
  COUNT(*) as total,
  SUM(t1.submit_date IS NULL) AS num_incomplete,
  AVG(IF(t1.submit_date IS NULL,
         DATEDIFF(t1.due_date, NOW()),
         NULL)) AS avg_incomplete_due_in,
  AVG(DATEDIFF(due_date,submit_date)) AS avg_complete_turnaround
FROM table_1 t1
INNER JOIN table_2 t2 ON t2.fid = t1.id
WHERE t1.due_date <= '2010-12-31'
GROUP BY t2.lender_name

Double Aggregate Function Mysql

Here you go:

select a.id, 
       a.section,
       a.magic_count
from (
    select p.id,
           p.section,
           magic_count
    from (
        select m.product, sum(count) as magic_count
        from matrix m
        group by m.product
    ) sm
    join products p on sm.product = p.id
) a
left join (
    select p.id,
           p.section,
           magic_count
    from (
        select m.product, sum(count) as magic_count
        from matrix m
        group by m.product
    ) sm
    join products p on sm.product = p.id
) b on a.section = b.section and a.magic_count < b.magic_count
where b.id is null

• see a simplified example (and other methods) in the manual entry for The Rows Holding the Group-wise Maximum of a Certain Column

• see it working live here

How to join tables with aggregate functions in MYSQL query?

Use a correlated subquery to get the last record for each id in timelog:

SELECT e.*, tl.*
FROM employee e JOIN
     timelog tl
     ON e.id = tl.id
WHERE tl.count = (SELECT MAX(tl2.count)
                  FROM timelog tl2
                  WHERE tl2.id = tl.id
                 ) ;

Note that your version of the query is not correct. The subquery returns the maximum count for each id. However, the outer query might match a different id to the count in the subquery. The correlation clause fixes this problem.

---

Related Topics