Re: Should be simple but....

From: Roji. P. Thomas (thomasroji_at_gmail.com)
Date: 11/11/04 

Date: Thu, 11 Nov 2004 16:41:07 +0530

Here is an example using Northwind

DECLARE @CustID VARCHAR(10)
SET @CustId ='ALFKI'

SELECT * FROM
(
SELECT C.ContactName, Sum(OD.UnitPrice * OD.Quantity) as TotalPurchase
FROM Customers C
INNER JOIN ORDERS O
ON O.CustomerID = C.CustomerId
INNER JOIN [ORDER DETAILS] OD
ON O.OrderId = OD.OrderID
WHERE C.CustomerId = @CustId
GROUP BY C.ContactName
UNION ALL
SELECT TOP 2 C.ContactName, Sum(OD.UnitPrice * OD.Quantity) as TotalPurchase
FROM Customers C
INNER JOIN ORDERS O
ON O.CustomerID = C.CustomerId
INNER JOIN [ORDER DETAILS] OD
ON O.OrderId = OD.OrderID
GROUP BY C.ContactName
HAVING Sum(OD.UnitPrice * OD.Quantity) <
(SELECT Sum(OD.UnitPrice * OD.Quantity)
FROM Customers C
INNER JOIN ORDERS O
ON O.CustomerID = C.CustomerId
INNER JOIN [ORDER DETAILS] OD
ON O.OrderId = OD.OrderID
WHERE C.CustomerId = @CustId
GROUP BY C.ContactName)
UNION ALL
SELECT TOP 2 C.ContactName, Sum(OD.UnitPrice * OD.Quantity) as TotalPurchase
FROM Customers C
INNER JOIN ORDERS O
ON O.CustomerID = C.CustomerId
INNER JOIN [ORDER DETAILS] OD
ON O.OrderId = OD.OrderID
GROUP BY C.ContactName
HAVING Sum(OD.UnitPrice * OD.Quantity) >
(SELECT Sum(OD.UnitPrice * OD.Quantity)
FROM Customers C
INNER JOIN ORDERS O
ON O.CustomerID = C.CustomerId
INNER JOIN [ORDER DETAILS] OD
ON O.OrderId = OD.OrderID
WHERE C.CustomerId = @CustId
GROUP BY C.ContactName)
)T ORDER BY T.TotalPurchase 

-- 
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Geir Holme" <geir@multicase.no> wrote in message 
news:eISn7t9xEHA.908@TK2MSFTNGP11.phx.gbl...
> Hi all.
> I have a litle chalange picking 5 rows sumarizing the price and group by 
> the customer. I want my list to put my spesifik customer into the midle 
> row (3) and the 2 customer that has shopped for more in the next rows (4 
> and 5) and the 2 customers that has shopped for less in the 2 first rows 
> (1 and 2). I am supposed to show this in a graph so the customer in 
> question can see how much he has shopped for AND also see the 2 customers 
> closest on both sides. This is used in a competition to show how far it is 
> to get one place up (or down).
> I hope anyone understood my chalange here.
>
> I do know how to do this, but not how to do it in an efficent way. (Using 
> loops, tests and stuff)
>
> thanks all
> -gh
>

Relevant Pages

  • RE: How can I count the number of individuals?
    ... where Customers is the name of the table. ... To count those who match certain criteria add a WHERE clause, ... FROM Customers INNER JOIN Cities; ... Four tables are involved here Customers, Orders, Order Details and Products. ...
    (microsoft.public.access.queries)
  • RE: datetime calculation
    ... Declare @OrderDate datetime ... Inner Join [Order Details] od On p.ProductID = od.ProductID ... Inner Join Orders o On od.OrderID = o.OrderID ...
    (microsoft.public.sqlserver.dts)
  • Re: Simple, free RDBMS for VB3/6 with minimal installation footprint
    ... INNER JOIN Customers C ON ... O.ShipName from Customers C inner join Orders O on ... this is not a failing of the wrapper code. ...
    (microsoft.public.vb.general.discussion)
  • Re: SQL question ?
    ... FROM Customers c INNER JOIN Orders o ON c.CustomerID = o.CustomerID ... front of each column makes the SQL way too wordy. ...
    (microsoft.public.vb.general.discussion)
  • Re: SQL question ?
    ... > FROM Customers c INNER JOIN Orders o ON c.CustomerID = o.CustomerID ... > front of each column makes the SQL way too wordy. ...
    (microsoft.public.vb.general.discussion)