How to hide duplicates - base query Percentages in SQL using partition over in Firebird database

[melvin7020]

How do you hide duplicates when using sum over and partition. Is there a work around?

Am back down the percentage rabbit hole. I have a table that lists random stuff, with a column with an integer set to 1, to filter the said random stuff.

I have a query which uses sum over and partition to count

• how many of the random stuff there are.
• How much stuff there is in total
• the percentage of that random stuff
• also am playing with using case to generate an ID for the random stuff.

but it all goes to the wind when I try to group the duplicates.

Is there a work around, fix? What am I missing?
zz anoterh percentage attempt v4.odb (8.6 KB)

[Villeroy]

What is a duplicate in that spreadsheet dump? Duplicate “Ref No”, duplicate Type or a duplicate combination of both? A database can prevent storing duplicates, so you never have to bother about them.

[melvin7020]

how do you put da code in da boxers? oooh you highlight it then press block quotes!

“Type faery dragon da wind”

if i put a DISTINCT in at the SELECT bit at da top

SELECT “TrainID”, “Ref No”, distinct “Type faery dragon da wind”, “ForFiltering”, “notes”,

SUM( “ForFiltering”*1.0000 ) OVER ( PARTITION BY “Type faery dragon da wind” ) as “how many”,
(SELECT SUM( “ForFiltering” ) FROM “01_magicCreatures”) as “table total”,

SUM( “ForFiltering”*1.0000 ) OVER ( PARTITION BY “Type faery dragon da wind” )
/
(SELECT SUM( “ForFiltering” ) FROM “01_magicCreatures”) as “Percentage”,

CASE
WHEN “Type faery dragon da wind” = ‘dragon’ THEN ‘01’
WHEN “Type faery dragon da wind” = ‘depth faery’ THEN ‘02’
when “Type faery dragon da wind” = ‘dragon’ THEN ‘03’
when “Type faery dragon da wind” = ‘faery’ THEN ‘04’
when “Type faery dragon da wind” = ‘green faery’ THEN ‘05’
when “Type faery dragon da wind” = ‘half depth faery’ THEN ‘06’
when “Type faery dragon da wind” = ‘mitar dragon’ THEN ‘07’
when “Type faery dragon da wind” = ‘smaller dragon’ THEN ‘08’
when “Type faery dragon da wind” = ‘replace me’ THEN ‘x’

ELSE ‘99’ END as Creature_Type_ID

FROM “01_magicCreatures”
’

I get a ‘isc_dsql_prepare’ error.

same if i try group by in the FROM at the bottom.

SELECT “TrainID”, “Ref No”, “Type faery dragon da wind”, “ForFiltering”, “notes”,

SUM( “ForFiltering”*1.0000 ) OVER ( PARTITION BY “Type faery dragon da wind” ) as “how many”,
(SELECT SUM( “ForFiltering” ) FROM “01_magicCreatures”) as “table total”,

SUM( “ForFiltering”*1.0000 ) OVER ( PARTITION BY “Type faery dragon da wind” )
/
(SELECT SUM( “ForFiltering” ) FROM “01_magicCreatures”) as “Percentage”,

CASE
WHEN “Type faery dragon da wind” = ‘dragon’ THEN ‘01’
WHEN “Type faery dragon da wind” = ‘depth faery’ THEN ‘02’
when “Type faery dragon da wind” = ‘dragon’ THEN ‘03’
when “Type faery dragon da wind” = ‘faery’ THEN ‘04’
when “Type faery dragon da wind” = ‘green faery’ THEN ‘05’
when “Type faery dragon da wind” = ‘half depth faery’ THEN ‘06’
when “Type faery dragon da wind” = ‘mitar dragon’ THEN ‘07’
when “Type faery dragon da wind” = ‘smaller dragon’ THEN ‘08’
when “Type faery dragon da wind” = ‘replace me’ THEN ‘x’

ELSE ‘99’ END as Creature_Type_ID

FROM “01_magicCreatures” group by “Type faery dragon da wind”’

it works fine with just
'>

SELECT “TrainID”, “Ref No”, “Type faery dragon da wind”, “ForFiltering”, “notes”,

SUM( “ForFiltering”*1.0000 ) OVER ( PARTITION BY “Type faery dragon da wind” ) as “how many”,
(SELECT SUM( “ForFiltering” ) FROM “01_magicCreatures”) as “table total”,

SUM( “ForFiltering”*1.0000 ) OVER ( PARTITION BY “Type faery dragon da wind” )
/
(SELECT SUM( “ForFiltering” ) FROM “01_magicCreatures”) as “Percentage”,

CASE
WHEN “Type faery dragon da wind” = ‘dragon’ THEN ‘01’
WHEN “Type faery dragon da wind” = ‘depth faery’ THEN ‘02’
when “Type faery dragon da wind” = ‘dragon’ THEN ‘03’
when “Type faery dragon da wind” = ‘faery’ THEN ‘04’
when “Type faery dragon da wind” = ‘green faery’ THEN ‘05’
when “Type faery dragon da wind” = ‘half depth faery’ THEN ‘06’
when “Type faery dragon da wind” = ‘mitar dragon’ THEN ‘07’
when “Type faery dragon da wind” = ‘smaller dragon’ THEN ‘08’
when “Type faery dragon da wind” = ‘replace me’ THEN ‘x’

ELSE ‘99’ END as Creature_Type_ID

FROM “01_magicCreatures”

’

but then there are 158 individual records for da dragons.

[Wanderer]

@melvin7020

how do you put da code in da boxers? oooh you highlight it then press block quotes!

No, use a line with three backtics at beginning and end of your box. This avoids the “beautiful” curly quotes.

Can typographic quotes be switched off for ask.libreoffice.org?

The ```text though was to force the code block to monochrome as multiple functions in Basic code somehow lead to an ugly all red formatting starting at the second function block. Usually a simple ``` without any trailing text (not even blanks) on its own line does some nicely automatically coloured formatting. One can force a language though like ```cpp ```bash ```java but apparently not for Basic.

[Villeroy]

zz anoterh percentage attempt v6.odb (34.0 KB) (added the overall count of records to the “Types” form)

[cpb]

@melvin7020,
.
why are you using window functions for this?
just drop the columns which cannot be grouped (“TransID”,“Ref No”,“notes”)

select
	"Type faery dragon da wind",
	count(*) "HowMany",
--	(select count(*) from "01_magicCreatures") "TableTotal",
	100.00 * count(*) / (select count(*) from "01_magicCreatures") "Percentage"
from
	"01_magicCreatures"
group by
	"Type faery dragon da wind"
order by
	"Type faery dragon da wind"

[melvin7020]

Thank you for your efforts.

I am interested in percentage probabilities. I am trying to recreate a spread sheet which listed results, and their variables. With the spread sheet I was able to filter out different variable and see the effect that had on the probablities.

With the windows functions I can drill down and see the individual records. Unfortunately, when linked to a form and sub form, it shows only the probability associated with that record, and not the other probabilities as well. I wan to see the 2nd, 3rd and 4th highest percentages as well.

I am wondering if I can use lag or lead to fitler our duplicates and maybe to list other probabilities. My attempts so far have come up with the SQL Status: HY000 error when I try and put a lead or lag with something else.

What does that error mean? It is listed here 7.8  Trapping and Handling Errors

[Villeroy]

Store structured data in a database. Run statistics on selected, filtered, ordered records in a statistics program. Every statistics program can interface with a standard database program. If your elected statistics program is a spreadsheet, so be it. Calc can not filter, sort and combine related record sets. However, it is very easy to feed calculation models with well prepared record sets.
[Tutorial] Using registered datasources in Calc

[cpb]

@melvin7020,
.
firstly you can not use ‘GROUP BY’ or ‘DISTINCT’ at the same level as any window function.
.
your queries simply duplicate your table plus 2 calculated fields “how many” and “Percentage”, records are in no particular order.
.

1. how many of the random stuff there are. DONE.
2. How much stuff there is in total. DONE.
3. the percentage of that random stuff. DONE.
4. also am playing with using case to generate an ID for the random stuff. DONE.

@melvin7020

but then there are 158 individual records for da dragons

yes of course there are, how could there not be?

@melvin7020

sub form, it shows only the probability associated with that record

the sub-form shows records from its data-source filtered by the value of the linked field/fields.
the data-source may be a table or derived table(view,query).
.
in a nutshell:
you have exactly what you asked for.
obviously you wish to extract some sort of result from your derived data but no one has a clue what that may be.
if you can not be specific textually or show an illustrative example which details your aims then it’s impossible to help.

[Villeroy]

@melvin7020

WHEN “Type faery dragon da wind” = ‘dragon’ THEN ‘01’
WHEN “Type faery dragon da wind” = ‘depth faery’ THEN ‘02’

Store all these name/value pairs in a table and everything will be fine with a most simple query. We still don’t know what a duplicate is.

[melvin7020]

Thanks Villeroy, you inspired me to go back to my spread sheet and play around with it some more. I have redesigned it so it is easier to use, if the glowing temptation of a database doing what I want, is still far off. It seems so close…

CBP thanks for your efforts, and I am sorry if my lack of clarity frustrated you. It is very clear in my head, what I mean, but obviouysly you can not see into my head. (mentalics anyone?)
I attach a spread sheet as an illustrative example. This is what I am trying to replicate in the database.

I am filtering by two random columns, and when filtereed the result tells me the number and percentage of the magical creatures swayed. I am using one column to order and re-order the list, (this is what came of your (villeroy’s) inspiration, redesigning this so that it is more managable in my spread sheet. On the second sheet, I have my sorting techniques layed out in a more legible way. I am hiding most of the records, (which is why I said

“there are 158 individual records for the dragon”).

This aritcles mentions rollup? SQL Tip: Creating a Grand Total (and additional subtotals) – Benjamin's Blog Also, is there some join I could do that would let me link the summary to the record? I played a bit with lead and lag, wondering if I could put all the other percentages all together. Also, that was why I was thinking about using a sub form? I have not used access for years, but in that (if I am remembering correctly) you could actually put in a sub form and if it was not linked, it just sat there. Is there some work around like that in base?

Is there something obvious I am missing? Or some juicey nugget of SQL that will thrill and solve this? It feels so near…

Thank you for your help and interest. It is so wonderful not watchin eyes glaze over when I talk about spread sheets and now databases!
zz anoterh percentage attempt v5 table .ods (93.9 KB)