How to Calculate Top Customer in each City With Maximum order

Question

I had create table name customers orders and orderdetails as follow

CREATE TABLE `customers` (
  `customerNumber` int(11) NOT NULL,
  `customerName` varchar(50) NOT NULL,
  `contactLastName` varchar(50) NOT NULL,
  `contactFirstName` varchar(50) NOT NULL,
  `phone` varchar(50) NOT NULL,
  `addressLine1` varchar(50) NOT NULL,
  `addressLine2` varchar(50) default NULL,
  `city` varchar(50) NOT NULL,
  `state` varchar(50) default NULL,
  `postalCode` varchar(15) default NULL,
  `country` varchar(50) NOT NULL,
  `salesRepEmployeeNumber` int(11) default NULL,
  `creditLimit` double default NULL,
  PRIMARY KEY  (`customerNumber`)
)

CREATE TABLE `orders` (
  `orderNumber` int(11) NOT NULL,
  `orderDate` datetime NOT NULL,
  `requiredDate` datetime NOT NULL,
  `shippedDate` datetime default NULL,
  `status` varchar(15) NOT NULL,
  `comments` text,
  `customerNumber` int(11) NOT NULL,
  PRIMARY KEY  (`orderNumber`)
) 

CREATE TABLE `orderdetails` (
  `orderNumber` int(11) NOT NULL,
  `productCode` varchar(15) NOT NULL,
  `quantityOrdered` int(11) NOT NULL,
  `priceEach` double NOT NULL,
  `orderLineNumber` smallint(6) NOT NULL,
  PRIMARY KEY  (`orderNumber`,`productCode`)
)

After that I have to find out top ordered customer from each city.I had calculated total order by each customer as given below but I don't know how to select top one customer from each city with maximum order. So Question is how could I achive that.

SELECT o.customerNumber, ROUND( SUM( od.priceEach * od.quantityOrdered ) , 2 ) AS totalOrder
FROM orders AS o
INNER JOIN orderDetails AS od ON o.orderNumber = od.orderNumber
GROUP BY o.customerNumber
ORDER BY totalOrder DESC

I'm using mysql.

I also heard about `partition`... could we solve this using `partition` or not.If we can solve then which method is efficient in reference to time for large data table... — Lionel, Dec 12 '12 at 11:16

Sashi Kant · Answer 1 · 2012-12-12T11:28:08.623

1

Try this::

Select tempTable.custNum, MAX(tempTable.totalOrder), customers.city
from
(
SELECT 
o.customerNumber as custNum, 
ROUND( SUM( od.priceEach * od.quantityOrdered ) , 2 ) AS totalOrder,
city
FROM orders AS o
INNER JOIN orderDetails AS od ON o.orderNumber = od.orderNumber
GROUP BY o.customerNumber
ORDER BY totalOrder DESC) as tempTable
 inner join customers on (tempTable.custNum = customers.customerNumber)
group by tempTable.city

edited Dec 12 '12 at 11:28

answered Dec 12 '12 at 10:36

Sashi Kant

13,277
9
44
71

phpmyadmin says [Every derived table must have its own alias](http://stackoverflow.com/questions/3363918/mysql-error-1248-42000-every-derived-table-must-have-its-own-alias) – Lionel Dec 12 '12 at 10:48
there is no `city` field on `orders` and `orderDetails` table – Lionel Dec 12 '12 at 11:09
I thought you can do the join with the customer's table, Please dont expect an end to end solution from our side, By the way, have updated the query, check now – Sashi Kant Dec 12 '12 at 11:29

score 1 · Accepted Answer · edited May 23 '17 at 12:12

You can do this as Sashi Kant suggested but some edit is required on that answer

SELECT x.customerName, MAX( p.totalOrder ) , x.city
FROM (

SELECT o.customerNumber, ROUND( SUM( od.priceEach * od.quantityOrdered ) , 2 ) AS totalOrder
FROM orders AS o
INNER JOIN orderDetails AS od ON o.orderNumber = od.orderNumber
GROUP BY o.customerNumber
ORDER BY totalOrder DESC
)p
INNER JOIN customers x ON p.customerNumber = x.customerNumber
GROUP BY city

yednamus · Answer 3 · 2012-12-12T13:14:22.977

0

select max(z.total),z.customerNumber,z.city,z.customerName from (select c.customerNumber,c.city,c.customerName, 
   sum(od.priceEach * od.quantityOrdered) as total 
   from customers c, orders o, orderdetails od 
   where c.customerNumber = o.customerNumber 
      and o.orderNumber = od.orderdetails 
   group by c.customerNumber) as z  group by z.customerNumber,z.city

edited Dec 12 '12 at 13:14

answered Dec 12 '12 at 11:05

yednamus

582
1
4
22

How to Calculate Top Customer in each City With Maximum order

3 Answers3