CODEWITHSUNDEEP
mysql
Lionel
Lionel

Reputation: 596

How to Calculate Top Customer in each City With Maximum order

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.

Upvotes: 0

Views: 170

Answers (3)

yednamus
yednamus

Reputation: 583

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

Upvotes: 0

Sashi Kant
Sashi Kant

Reputation: 13465

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

Upvotes: 1

nKandel
nKandel

Reputation: 2575

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

Upvotes: 1

Related Questions