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.
Count lowest number in a range
Count lowest number in a range
Last edited by StormWolf on Sun Dec 06, 2020 11:31 pm, edited 1 time in total.
Open Office 4.1.3 Using Windows 10
Re: Count lowest number in a range
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")
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Re: Count lowest number in a range
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"
========
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"
========
Open Office 4.1.3 Using Windows 10
Re: Count lowest number in a range
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.
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.
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.
Code: Select all
SELECT "tbl chart"."album id",
Min("tbl chart"."number") AS "HP"
FROM "tbl chart"
GROUP BY "tbl chart"."album id"
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"
I expect there is a mistake somewhere in my query because I haven't written queries in many months and I am getting rusty.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Re: Count lowest number in a range
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.
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"
open office 4.1.14 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.6.0 (Split) on Windows 10