Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – is't right in classic models find customers that have orderd most. I don't find customers that have ordered the same quantity

select productline, customerName, totale 
from(
    SELECT productLines.productline, 
         customers.customerName,
         sum(quantityOrdered) as totale 
    from customers, orders, productlines, products, orderdetails 
    WHERE customers.customerNumber = orders.customerNumber 
       and orderdetails.orderNumber= orders.orderNumber 
       and orderdetails.productCode = products.productCode 
       and productlines.productLine= products.productLine 
    GROUP by productlines.productline, customers.customerName 
    order by sum(quantityOrdered) desc 
   ) as lista 
group by productline;

in practice the query works well but I had to use a subquery which must give ordered results and in my opinion it doesn’t work if there are two customers who have ordered the same number of pieces for the same type.
Can anyone tell me how to solve this problem. I tried with where total = (select but it gives me an error

Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    select productline, customerName, sum(totale)

    from ( SELECT productLines.productline, customers.customerName, sum(quantityOrdered) as totale from customers, orders, productlines, products, orderdetails WHERE customers.customerNumber = orders.customerNumber and orderdetails.orderNumber= orders.orderNumber and orderdetails.productCode = products.productCode and productlines.productLine= products.productLine GROUP by productline , customerName ) as lista

    where totale = ( SELECT max(totale1) FROM ( SELECT productLines.productline, customers.customerName, sum(quantityOrdered) as totale1 from customers, orders, productlines, products, orderdetails WHERE customers.customerNumber = orders.customerNumber and orderdetails.orderNumber= orders.orderNumber and orderdetails.productCode = products.productCode and productlines.productLine= products.productLine GROUP by productline , customerName ) as x

    )

    GROUP by productline;

    one step forward... but return

    productline customerName sum(totale) Classic Cars Euro+ Shopping Channel 3816

    it s right but only one :(

    idea???


  2. 0

    select * from
    (
    SELECT productLines.productline, customers.customerName, sum(quantityOrdered) as totale1 from customers, orders, productlines, products, orderdetails WHERE customers.customerNumber = orders.customerNumber and orderdetails.orderNumber= orders.orderNumber and orderdetails.productCode = products.productCode and productlines.productLine= products.productLine
    GROUP by productline , customerName
    ) as a

    left join

    (

    SELECT productLines.productline, customers.customerName, sum(quantityOrdered) as totale1 from customers, orders, productlines, products, orderdetails WHERE customers.customerNumber = orders.customerNumber and orderdetails.orderNumber= orders.orderNumber and orderdetails.productCode = products.productCode and productlines.productLine= products.productLine
    GROUP by productline , customerName
    ) as b

    on a.productline = b.productline and a.totale1 < b.totale1

    where isnull(b.totale1);

    ok resolved

    Login or Signup to reply.
  3. 0

    As I stated in my comment above, your query is non-deterministic and only working because you have ONLY_FULL_GROUP_BY disabled. Bill gives a nice simple explanation of the problem with MySQL’s behaviour with ONLY_FULL_GROUP_BY disabled here.

    Assuming you just want the top customer(s) for each productline you can use GROUP BY and RANK():

    SELECT productLine, customerName, totale
FROM (
    SELECT pl.productLine, c.customerName,
        SUM(quantityOrdered) AS totale,
        RANK() OVER (PARTITION BY pl.productline ORDER BY SUM(quantityOrdered) DESC) AS rnk
    FROM customers c
    JOIN orders o
        ON c.customerNumber = o.customerNumber
    JOIN orderdetails od
        ON od.orderNumber = o.orderNumber
    JOIN products p
        ON od.productCode = p.productCode
    JOIN productlines pl
        ON pl.productLine = p.productLine
    GROUP BY pl.productline, c.customerName
) AS lista
WHERE rnk = 1;

    You can read more in the docs – MySQL Handling of GROUP BY

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search