Query to find a value in column b based on an aggregate function on column a?

Hi,

Suppose I have a table containing monthly sales figures from my shop
branches:

Branch Month Sales
-----------------------
London Jan 5000
London Feb 4500
London Mar 5200
Cardiff Jan 2900
Cardiff Feb 4100
Cardiff Mar 3500

The question I am trying to ask is this: in which month did each branch
achieve its highest sales? So I want a result set something like this:

Branch Month
----------------
London Mar
Cardiff Feb

I can do a "SELECT Branch, MAX(Sales) FROM MonthlySales GROUP BY Branch" to
tell me what the highest monthly sales figure was, but I just can't figure
out how to write a query to tell me which month corresponded to MAX(Sales).
Ideas anyone?

Cheers,

....Andy

re: Query to find a value in column b based on an aggregate function on column a?

Assuming that the PK is (branch, month):

SELECT branch, [month], sales
FROM BranchSales AS S
WHERE sales =
(SELECT MAX(sales)
FROM BranchSales
WHERE branch = S.branch)

(untested)

Remember that there could be more than one month per branch with equal
maximum sales.

--
David Portas
------------
Please reply only to the newsgroup
--

re: Query to find a value in column b based on an aggregate function on column a?

Hi

You could try:

SELECT Branch, Month, Sales
FROM MonthlySales
WHERE Sales =

( SELECT MAX(Sales) FROM MonthlySales )

If you need branch them:

SELECT M.Branch, M.Month, M.Sales
FROM MonthlySales M
WHERE M.Sales =

( SELECT MAX(S.Sales)
FROM MonthlySales S WHERE S.Branch = M.Branch )


John

"AndyL" <news@squit.co.uk.nospamplease.net> wrote in message
news:voq7iigetsa7ed@corp.supernews.com...[color=blue]
> Hi,
>
> Suppose I have a table containing monthly sales figures from my shop
> branches:
>
> Branch Month Sales
> -----------------------
> London Jan 5000
> London Feb 4500
> London Mar 5200
> Cardiff Jan 2900
> Cardiff Feb 4100
> Cardiff Mar 3500
>
> The question I am trying to ask is this: in which month did each branch
> achieve its highest sales? So I want a result set something like this:
>
> Branch Month
> ----------------
> London Mar
> Cardiff Feb
>
> I can do a "SELECT Branch, MAX(Sales) FROM MonthlySales GROUP BY Branch"[/color]
to[color=blue]
> tell me what the highest monthly sales figure was, but I just can't figure
> out how to write a query to tell me which month corresponded to[/color]
MAX(Sales).[color=blue]
> Ideas anyone?
>
> Cheers,
>
> ...Andy
>
>[/color]

re: Query to find a value in column b based on an aggregate function on column a?

Thank you David and John - that was what I was looking for.

I fettled it a bit thus:
SELECT branch, MIN([month]), sales
FROM MonthlySales AS S
WHERE sales =
(SELECT MAX(sales)
FROM MonthlySales
WHERE branch = S.branch)
GROUP BY branch, sales

to ensure I'd only get one record for each branch even if the maximum sales
figure was achieved in more than one month.

....Andy

re: Query to find a value in column b based on an aggregate function on column a?

In the sample data you posted you used literal month names. If that is how
your actual data looks then MIN([month]) will return Feb before Jan for
example. This may not matter if you just wanted an arbitrary top selling
month but it does illustrate one reason why storing months in this way is
not a very good idea.

Another problem is that you didn't mention a year column in your table -
without it this will obviously break in January.

Use DATETIME to represent months and years.

--
David Portas
------------
Please reply only to the newsgroup
--

re: Query to find a value in column b based on an aggregate function on column a?

David Portas wrote:[color=blue]
> In the sample data you posted you used literal month names. If that is how
> your actual data looks then MIN([month]) will return Feb before Jan for
> example. This may not matter if you just wanted an arbitrary top selling
> month but it does illustrate one reason why storing months in this way is
> not a very good idea.[/color]

Don't worry David, that was only example data! My real situation was too
complex to explain in a brief newsgroup posting, so I just made up the
example to illustrate the principle of the specific problem I was having.

....Andy