Count lowest number in a range

[StormWolf]

I am using Windows 10 and Open Office Base.

I'm looking to count the number of times the lowest number in a range occurs. Have table with column that has the minimum value, but i'd like to count how many times that minimum value occurs.

Not sure how to go about this.

Thanks.

[FJCC]

I am not sure I understand you request. Perhaps this is what you are looking for.

Code: Select all

SELECT COUNT("MyColumn") FROM "MyTable" 
WHERE "MyColumn" = (SELECT MIN("MyColumn") FROM "MyTable")

[StormWolf]

Hi. Below is the sql code I used to get the minimum of a range and it works fine. It finds the Minimum chart position of an album. I cannot for the life of me work out how to then count the number of times it was at HP (Highest Position). I remember Excel used a COUNTMIN function for this exact problem.

SELECT "tbl chart"."album id",
Min("tbl chart"."number") AS "HP"
FROM "tbl chart",
"tbl album title artist"
WHERE "tbl chart"."album id" = "tbl album title artist"."album id"
GROUP BY "tbl chart"."album id"
=====
Using FJCC's framework this is what I came up with but it just prints 55 records (all multiples of 496) from 496 to 13888. Removing the Group By only makes it add up to 159216. These aren't of much use though. Thanks FJCC for your effort but I just cant make it work.

SELECT Count("tbl chart"."number")
FROM "tbl chart",
"tbl album title artist"
WHERE "tbl chart"."number" = (SELECT Min("tbl chart"."number") FROM "tbl chart")
GROUP BY "album id"

========

[FJCC]

Your query to find the minimum number for each album seems to have a needless join to the table "tbl album title artist", though perhaps in the actual application you need some columns from that table. Here is a simplified version.

Code: Select all

SELECT "tbl chart"."album id",
Min("tbl chart"."number") AS "HP"
FROM "tbl chart"
GROUP BY "tbl chart"."album id"

Your second query includes the same join but no limiting conditions, so every row in the first table is joined to every row in the second table, producing confusing results. If you want to know how many times each album achieved its minimum "number", try something like this.

Code: Select all

SELECT "tbl chart"."album id", Count("tbl chart"."number")
FROM "tbl chart" INNER JOIN
(SELECT "tbl chart"."album id",
Min("tbl chart"."number") AS "HP"
FROM "tbl chart"
GROUP BY "tbl chart"."album id") AS "MinSubQuery"
ON "MinSubQuery"."album id" = "tbl chart"."album id" AND "MinSubQuery"."HP" = "tbl chart"."number"
GROUP BY "album id"

The idea is that MinSubQuery returns a table listing each album and its HP. Joining that to "tbl chart" keeps only the rows where "number" is equal to "HP" for each album.
I expect there is a mistake somewhere in my query because I haven't written queries in many months and I am getting rusty.

[chrisb]

StormWolf,
it's not a good idea to include spaces in table/field names.
if i got the field/table names right then this should do the job.
paste it here>>> 'Queries>Create Query in SQL View'. Hit the 'SQL' icon. Hit 'F5' (execute).
ensure that it runs OK before you edit the code.

Code: Select all

select
--a."ReplaceWithFieldNameWhichHoldsAlbumTitleAndDeletePreceeding'--'", 
a2."album id", a2."number", a2."Count" from
(
	select "album id", min("number") "MinPos"
	from "tbl chart"
	group by "album id"
) a1

join
(
	select "album id", "number", count(*) "Count"
	from "tbl chart"
	group by "album id", "number"
) a2
on a2."album id"=a1."album id" and a2."number"=a1."MinPos"
join "tbl album title artist" a on a."album id"=a1."album id"