15,940,921 members
1.00/5 (1 vote)
See more:
I have a data set (AdventureWorks db) with fields like 'totalDue, CustomerID, OrderDate, OrderNumber'. I have to show the top customer and total amount they spent over any 2 quarter period from the 3 years (07/01/2005 to 07/31/2008) available in the data. We were told that it can be a range of any period of 9 consecutive months, but for like every year. And perhaps the best way to do it is with "Over BY Partition function".

I have tried numerous ways, but I am not getting the right answer.

What I have tried so far isn't giving me the right answer. The answer is supposed to be CustomerID: 29641. And we are also supposed to show the total amount spent by that Customer, but we weren't given that part of the answer.

Can anyone give me an insight to this problem please?

What I have tried:

```select top 1 CustomerID,
Sum(TotalDue) Over (Partition By dateadd(qq, 3, OrderDate)) as TotalSpent
Posted
Updated 31-Jan-17 7:03am
v2
How do we know that the customer ID must be that one, we have no idea how your tables are structured.

Besides, to also get the total amount spent, just add that as column query (such as SUM(amount_spent). But remember, for aggregate functions you also need to use grouping in SQL.

## Solution 1

It might have been best if you had just typed in what your homework assignment said because you didn't explain it very well.

However, first problem - you are using `Partition By` for the quarter, but you also need to partition by the `CustomerID`
SQL
`Sum(TotalDue) Over (Partition By CustomerID, dateadd(qq, 3, OrderDate)) as TotalSpent`
Also, you have used `top 1` but you have not ordered the results so the single row returned is quite random. You need to include an `order by` clause e.g.
SQL
`order by Sum(TotalDue) Over (Partition By CustomerID, dateadd(qq, 3, OrderDate)) desc`
OR
SQL
`order by 2 desc`
will also work - read this as "order by the 2nd item in the SELECT list, highest value first"

I'm not sure if the overall total for that customer is supposed to be in the same query or not - if it is then include another `Sum` using `Partition By` but this time you don't need to partition by the quarter.

That should be enough to get you moving on this again