Using SUM With MAX

[dlebowski]

Hi.  I have a table that looks like the following:

 

+------+---------------+

| Price | ItemNumber |

+------+---------------+

| 1      | 33            |

| 2      | 66            |

| 3      | 66            |

| 4      | 44            |

| 5      | 44            |

| 6      | 44            |

+------+---------------

 

What I want to do is takes the highest Price for each ItemNumber, and sum those values.  For example:

 

1 - 33

3 - 66

6 - 44

---------

Total:  10

 

I am assuming that I am going to use MAX function and SUM, but how do I use both.  Here is my query:

 

SELECT MAX(Price), ItemNumber FROM mytable GROUP BY ItemNumber ORDER BY ItemNumber

 

This query gives me this:

 

1 - 33

3 - 66

6 - 44

---------

 

 

So how do I use that same query to give me a SUM of those values?  Than you in advance!

[awjudd]

Something like this should work:

 

SELECT SUM(MaxPrice) AS TotalPrice

FROM ( SELECT MAX(Price) AS MaxPrice FROM mytable GROUP BY ItemNumber )

 

Basically running your max in the sub query and then based on those values, sum them up

 

~juddster