Selection of output

[subs]

Lane Transport amt
A 560
A 890
A 560
A 560
B 890
C 740
C 760


Output required

Lane Tansport Amt
A 560
B 890
C 750

what will be the SQL query? the query must select the highest
frequency transport amt for every lane whenever there is a repeat of
transport amt. for example for lane A- 560 is repeated thrice. so 560
should be selected for A.
For B, there is only one amt so 890 should be selected. For C there is
no repeat, so in this case, avg should be taken i.e avg of 740 and
760.

what will be query which can satisfy all conditions and can come up
the output like above?

Please help

 

[John Spencer]

Three query solution

Q1 : Get Amounts and frequency count of amounts
SELECT Lane, [Transport Amt] as Amount
, Count(Transport Amt) as TheCount
FROM TheTable
GROUP BY Lane, [Transport Amt]

q2 : Get Lane and the highest frequency count
SELECT Lane, Max(TheCount) BigCount
FROM q1
Group by Lane

Q3 ; Join the two queries together and get the average of the amounts.
SELECT q1.Lane, Avg(Q1.Amount) as theAmount
FROM Q1 Inner Join Q2
ON Q1.Lane = Q2.Lane
AND q1.TheCount = Q2.BigCount
GROUP BY Lane

Average of amounts that are all the same will be the same as the value
of one amount. Otherwise the average will be the average of all the
amounts that have the same frequency count.

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