MS Access SQL to return most frequently appearing values. 1

[WildbloodS]

In a summary query, I need to return the value that shows up the greatest number of times in a certain column of a table "TotalAssetsCalculated", for each route that is listed in a separate column "Route"

I have read that I need to calculate the "mode", and the approach I am using is cribbed from another forum

SELECT TOP 1 myTable.TotalAssetsCalculated, myTable.Route
FROM myTable
GROUP BY myTable.TotalAssetsCalculated, myTable.Route
HAVING (((myTable.TotalAssetsCalculated) Is Not Null))
ORDER BY Count(*) DESC;

I am getting the result for the highest appearing value across the whole dataset.

TotalAssetsCalculated

​

Route
5,000.00

​

Wales

When I would like to see the values for all the routes, e.g. 2500 Anglia, 1300 Western

Any pointers gratefully received, thank you !

 

[Andrzejek]

Could you show a sample of your data?

Just a guess here:
SELECT MAX(TotalAssetsCalculated) As ABCD, Route
FROM myTable
GROUP BY Route


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[WildbloodS]

Hi Andy
Thanks for this - unfortunately MAX seems to return the highest value within Route rather than the most frequently occurring within Route

Anglia 6,004.00
East Midlands 4,829.00
Kent 4,139.00


The results I am after would look like this:

Anglia 6,003.89
East Midlands 4,828.87
Kent 4,138.42

I have posted a data sample below.
At present the Top 1 (mode) function just returns
Anglia 6,003.89
- Because it appears 11 times
(East Midlands 4,828.87 10 times, Kent 4,138.42, 9 times)

I am a pretty infrequent user of Access SQL; I tried adapting the "As ABCD" function and got nowhere... any help very welcome.

Route TotalAssets_Calculated
Anglia 5,000.00
Anglia 5,000.00
Anglia 6,000.00
Anglia 6,003.89
Anglia 6,003.89
Anglia 6,003.89
Anglia 6,003.89
Anglia 6,003.89
Anglia 6,003.89
Anglia 6,003.89
Anglia 6,003.89
Anglia 6,003.89
Anglia 6,003.89
Anglia 6,003.89
Anglia 6,004.00
East Midlands 4,828.87
East Midlands 4,828.87
East Midlands 4,828.87
East Midlands 4,828.87
East Midlands 4,828.87
East Midlands 4,828.87
East Midlands 4,828.87
East Midlands 4,828.87
East Midlands 4,828.87
East Midlands 4,828.87
East Midlands 4,829.00
Kent 4,000.00
Kent 4,138.42
Kent 4,138.42
Kent 4,138.42
Kent 4,138.42
Kent 4,138.42
Kent 4,138.42
Kent 4,138.42
Kent 4,138.42
Kent 4,138.42
Kent 4,139.00

Thanks again

 

[Andrzejek]

A good place to start would be here:

[pre]
Select Route, TotalAssets_Calculated, Count(*) As MyCount
FROM myTable
Group By Route, TotalAssets_Calculated

Route TotalAssets_Calculated MyCount
Anglia 5,000.00 2
Anglia 6,000.00 1[blue]
Anglia 6,003.89 11
East Midlands 4,828.87 10[/blue]
East Midlands 4,829.00 1
Kent 4,000.00 1[blue]
Kent 4,138.42 9[/blue]
Kent 4,139.00 1
[/pre]
Now we just need to get the BLUE records out of it...
[tt][blue]
Select Route, TotalAssets_Calculated, Max(MyCount)
From ([/blue] Select Route, TotalAssets_Calculated, Count(*) As MyCount
FROM myTable
Group By Route, TotalAssets_Calculated )[blue]
Group By Route, TotalAssets_Calculated[/blue]
[/tt]
SQL's not tested, just an idea

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[WildbloodS]

Thanks Andy, have tried with this but seem to be getting identical results for both..

"Mode1"

SELECT Route, TotalAssets_Calculated, Count(*) AS MyCount
FROM myTable
GROUP BY Route, TotalAssets_Calculated;


Then running "Mode2"

SELECT Mode1.Route, Mode1.TotalAssets_Calculated, Max(Mode1.MyCount) AS MaxMyCount
FROM Mode1
GROUP BY Mode1.Route, Mode1.TotalAssets_Calculated;


Showing all values, not the "Max" counts I was hoping to see in Mode2.

 

[Andrzejek]

Based on the sample provided, what do you get as the outcome from
"Mode1":

Select Route, TotalAssets_Calculated, Count(*) As MyCount
FROM myTable
Group By Route, TotalAssets_Calculated

???

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[WildbloodS]

Hi Andy
Seems to be this:

Route TotalAssets_Calculated MaxMyCount
Anglia 6,003.89 11
Anglia 5,000.00 2
Anglia 6,004.00 1
Anglia 6,000.00 1
East Midlands 4,828.87 10
East Midlands 4,829.00 1
Kent 4,138.42 9
Kent 4,139.00 1
Kent 4,000.00 1

 

[Andrzejek]

WildbloodS,
In addition to what I had so fat, I've got to this point:
[tt]
SELECT Route, Max(MyCount) As MyMax
FROM (SELECT Route, TotalAssets_Calculated, Count(*) as MyCount
FROM MyTable
Group By Route, TotalAssets_Calculated)
GROUP BY Route;
[/tt]
and have this information:[pre]
Route MyMax
Anglia 11
East Midlands 10
Kent 9
[/pre]
But I cannot get it going any further :-(

We have to wait for somebody smarter than me to resolve it...
(Unless I will have a revelation over the weekend... but don't count on it)


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[WildbloodS]

Hey, thanks for your assistance Andy, much appreciated !

 

[lameid]

I'm not sure if you need 1 or two queries but if it works all in one, below. I suspect you might have to use TOP in a second query instead of that one. But in short, group by, count and sort the count descending and get the top x values.


SELECT TOP 3 Route, TotalAssets_Calculated, Count(*) AS MyCount
FROM myTable
GROUP BY Route, TotalAssets_Calculated
Order By MyCount DESC;

 

[Andrzejek]

Yes, but if he has in his data:[tt]
Yorkshire
Scotland
Wales
...[/tt]
The "TOP 3" approach will not work. :-(

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[lameid]

But if the data is ordered by MyCount, it should work fine, unless it doesn't work with it and you need a second query.... I so rarely use Top I don't remember without trial by fire.

 

[WildbloodS]

Thanks both for your comments, unfortunately I am on other work this week, but hoping to get back to this next week - much appreciated.

 

[WildbloodS]

Still working on this... thanks for your input

 

[WildbloodS]

Both
Thanks very much for sticking with this one. I have finally got a solution, cribbed from a stack overflow post


SELECT n.Route, TotalAssets_Calculated
FROM (SELECT Route, TotalAssets_Calculated, Count(TotalAssets_Calculated) as cnt
FROM myTable3 as t
GROUP BY Route, TotalAssets_Calculated
) AS t INNER JOIN (SELECT Route, max(cnt) as maxcnt
FROM (SELECT Route, TotalAssets_Calculated, Count(TotalAssets_Calculated) as cnt
FROM myTable3
GROUP BY Route, TotalAssets_Calculated
) as t
GROUP BY Route
) AS n ON (t.cnt = n.maxcnt) AND (t.Route = n.Route);



(Where there is More than one result per Route - I use a simple avg query for the 2 or more results)

Thanks again
Regards
Wildblood

 

[lameid]

Sorry I somehow missed the requirement in the OP... But that query looks good. I reformatted below so it is easier for us mere mortals to read.

SELECT n.Route, TotalAssets_Calculated
FROM (
      SELECT Route, TotalAssets_Calculated, Count(TotalAssets_Calculated) as cnt
      FROM myTable3 as t
      GROUP BY Route, TotalAssets_Calculated
      ) AS t 
   INNER JOIN 
     (
      SELECT Route, max(cnt) as maxcnt
      FROM (
            SELECT Route, TotalAssets_Calculated, Count(TotalAssets_Calculated) as cnt
            FROM myTable3
            GROUP BY Route, TotalAssets_Calculated
            ) as t
      GROUP BY Route
      ) AS n 
    ON (t.cnt = n.maxcnt) AND (t.Route = n.Route);