Count lowest number in a range

Creating tables and queries

Count lowest number in a range

Postby StormWolf » Sun Jul 08, 2018 1:00 pm

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.
Open Office 4.1.3 Using Windows 10
StormWolf
 
Posts: 2
Joined: Sun Jul 08, 2018 12:46 pm

Re: Count lowest number in a range

Postby FJCC » Sun Jul 08, 2018 2:31 pm

I am not sure I understand you request. Perhaps this is what you are looking for.
Code: Select all   Expand viewCollapse view
SELECT COUNT("MyColumn") FROM "MyTable"
WHERE "MyColumn" = (SELECT MIN("MyColumn") FROM "MyTable")
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7158
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Count lowest number in a range

Postby StormWolf » Fri Jul 27, 2018 10:15 am

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"

========
Open Office 4.1.3 Using Windows 10
StormWolf
 
Posts: 2
Joined: Sun Jul 08, 2018 12:46 pm

Re: Count lowest number in a range

Postby FJCC » Fri Jul 27, 2018 7:08 pm

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   Expand viewCollapse view
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   Expand viewCollapse view
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.
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7158
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Count lowest number in a range

Postby chrisb » Fri Jul 27, 2018 8:57 pm

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   Expand viewCollapse view
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.6 & LibreOffice 6.0.7.3 (x64) using HSQL 1.8.10 (Embedded) and HSQL 2.4.1 (Split) on Windows 10
chrisb
 
Posts: 179
Joined: Mon Jun 07, 2010 4:16 pm


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 1 guest