CODEWITHSUNDEEP
mysqlsql
Krippsen
Krippsen

Reputation: 3

GROUP BY within functions

I'm currently stuck with MySQL

SELECT 
  c.id_customer, 
  ROUND(SUM((o.commision_main + o.commision_delivery)/(
    SELECT COUNT(o.id_order)
    FROM orders AS o 
    INNER JOIN customers AS c 
      ON o.id_customer = c.id_customer 
    GROUP BY)), 2) AS profit 
FROM orders AS o 
INNER JOIN customers AS c 
  ON o.id_customer = c.id_customer 
GROUP BY c.id_customer

I would love to make this SQL to use the COUNT for each id_customer that was GROUP BYed as well.
Picture of result
And now it takes COUNT for every id_customer so it divides everything by 6 instead of 3 for id_customer 3, 2 for id_customer 1 and 1 for id_customer 66.
Help would be appreciated!

Upvotes: 0

Views: 54

Answers (2)

GarethD
GarethD

Reputation: 69819

Your query seems needlessly complex, I am pretty sure you can get the result you are after with simply

SELECT  o.id_customer, 
        ROUND(AVG(o.commision_main + o.commision_delivery) , 2) AS Profit 
FROM    Orders AS o 
GROUP BY o.id_customer;

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133400

You should avoid the subquery for count() and use count() directly in main query

SELECT 
  c.id_customer, 
  ROUND(SUM((o.commision_main + o.commision_delivery)/COUNT(o.id_order)), 2) AS profit 
FROM orders AS o 
INNER JOIN customers AS c  ON o.id_customer = c.id_customer 
GROUP BY c.id_customer

Upvotes: 0

Related Questions