Most Common Price based on Max Value

  • Thread starter Thread starter Bruce
  • Start date Start date
B

Bruce

Hi,

Can somebody help me work out the Most Common Price based on the Value at
that price. Lets say I have a table called tbl_COS and it has the fields,
ImportDate, Price and Value.

1. The query should first sum the value on each day at a certain price level.
2. Next it should select the Price where Value is the Maximum for each day.

I have worked out step 1 as follows but am stuck on 2.

SELECT tbl_COS.ImportDate, tbl_COS.Price, Sum(tbl_COS.Value) AS SumOfValue
FROM tbl_COS
GROUP BY tbl_COS.ImportDate, tbl_COS.Price;


Here is some sample data. The correct result would be 0.91

ImportDate Price SumOfValue
20080327 0.775 88,720
20080327 0.780 972,482
20080327 0.785 972,782
20080327 0.790 391,586
20080327 0.795 388,124
20080327 0.800 19,730
20080327 0.900 275,782
20080327 0.905 296,296
20080327 0.910 1,064,228
20080327 0.915 554,944
20080327 0.920 331,747
20080327 0.925 128,751

Any help appreciated,

Bruce
 
SELECT tbl_COS.ImportDate, tbl_COS.Price
, Sum(tbl_COS.Value) AS SumOfValue
FROM tbl_COS
GROUP BY tbl_COS.ImportDate, tbl_COS.Price;

Step 2
SELECT Top 1 qa.*
FROM QueryOne as qa
ORDER BY SumOfValue Desc

You can probably do that all in one query

SELECT Top 1 tbl_COS.ImportDate, tbl_COS.Price
, Sum(tbl_COS.Value) AS SumOfValue
FROM tbl_COS
GROUP BY tbl_COS.ImportDate, tbl_COS.Price
ORDER BY Sum(tbl_COS.Value) Desc

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
Hi John,

Your sql works except I had overlooked an additional constraint, that being
I am looking for the price based on the max sum(value) for each customer. In
this case the customer = ASXCode. I have added the customer filed to the
following but it only selects the TOp 1 value for all customers instead of
for each customer.

SELECT TOP 1 tbl_COS.ImportDate, tbl_COS.Price, Sum(tbl_COS.Value) AS
SumOfValue, tbl_COS.ASXCode
FROM tbl_COS
GROUP BY tbl_COS.ImportDate, tbl_COS.Price, tbl_COS.ASXCode
ORDER BY Sum(tbl_COS.Value) DESC;

Any ideas on how I modify to get the same result for each customer of ASXCode?

Bruce
 
Perhaps, but what field identifies the customer and what table is that field in?

SELECT CustomerID
, tbl_COS.ImportDate
, tbl_COS.Price
, Sum(tbl_COS.Value) AS SumOfValue
, tbl_COS.ASXCode
FROM tbl_COS
GROUP BY CustomerID, tbl_COS.ImportDate, tbl_COS.Price, tbl_COS.ASXCode
ORDER BY Sum(tbl_COS.Value) DESC;

SELECT *
FROM AboveQuery as Q1
WHERE SumOfValue
IN (SELECT Top 1 SumOfValue
FROM AboveQuery as Q2
WHERE Q2.CustomerID = Q1.CustomerID
ORDER BY Q2.SumofValue DESC)

Or perhaps more efficient
SELECT *
FROM AboveQuery as Q1
WHERE SumOfValue
= (SELECT Max(SumOfValue)
FROM AboveQuery as Q2
WHERE Q2.CustomerID = Q1.CustomerID)


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
 
Back
Top