0

I need to return the Top 10 customers based on their revenue gain, while also displaying the number of their orders and the total weight of their orders.

First, we have the customer table, which stores the customer accounts. Second, we have the invoice table, which stores the revenue per order. This will be used to get the Top 10 customer, as they're ranked by the revenue. And finally, I have the order table, which stores the other details about the order, such as the weight.

I tried:

SELECT (c.account_number as Account, c.customer_name as Name, SUM(i.total_charge) as Revenue, COUNT(s.order) as NumberOfOrder, SUM(s.tot_weight) as Weight)
FROM t_customer as c
JOIN t_invoice as i ON c.account_number = i.account_number
JOIN t_shipment as s ON c.account_number = s.shipper_account 
WHERE s.ship_date BETWEEN '2016-08-01' AND '2016-08-31'
GROUP BY c.account_number
ORDER BY SUM(i.total_charge) DESC LIMIT 10

I thought I had it correct but it only returned SQL Error (1241): Operand should be 1 column(s). Thank you for your help.

Rheine
  • 77
  • 1
  • 8

1 Answers1

-1

Dont add parentheses in your select.

SELECT c.account_number as Account, c.customer_name as Name, SUM(i.total_charge) as Revenue, COUNT(s.order) as NumberOfOrder, SUM(s.tot_weight) as Weight
FROM t_customer as c
JOIN t_invoice as i ON c.account_number = i.account_number
JOIN t_shipment as s ON c.account_number = s.shipper_account 
WHERE s.ship_date BETWEEN '2016-08-01' AND '2016-08-31'
GROUP BY c.account_number
ORDER BY SUM(i.total_charge) DESC LIMIT 10

Hope it will help.

Pirate
  • 2,886
  • 4
  • 24
  • 42
  • This query is against the sql standards and will only run in MySQL under certain sql mode settings. The default settings of the current MySQL versions luckily prevent such monsters from running. – Shadow Aug 26 '16 at 09:06