Base, query using SQL, over and partition clause, problem whilst trying to calculate a percentage

English
, , , ,
#1

how do you do percentages in a query with lots of sub groups? I have a table with firebird. I have tried SQL but it uses OVER and PARTITION, but they don’t seem to be working on my computer. The Over and Partition come out green, does that mean something? When I run the query, they do not seem to do anything.

The percentage sqL I want to try is

SELECT
“Rando stuff”,
“how much”,
“Play time”,

SUM (“how much”) OVER (PARTITION BY “Rando stuff”) as ThingsTotal,
“how much”/SUM(“how much”) OVER (PARTITION BY “Rando stuff”) as “Percentage”

FROM “PercentageMe”

Would this code work? Would my percentage dreams come true? Would the tempation of a simple calc sheet be gone for good? Have I made some type of error. Is there some other alternative I can use in base? I can not even get this running total to work.

SELECT “Id”, “name”, “gender”, “salary”,

sum(“salary”) over (Partition by “gender”) as “RunningTotal”

FROM “RunMe”

Is there something more I need to do? When I do edit Run, things happen. When I press the run SQL command directly, nothing happens. Is there a mystery box to tick somewhere in the settings.

Thanks.

#2

A example would help.
Code for the second example will run here.
You won’t need to set it to direct SQL.

#3

Here you go

zz anoterh percentage attempt v1.odb (4.2 KB)

#4

Problem in the query is: You devide an integer by and integer and will get only integer.

…"how much" * 1.0000 / 
SUM( "how much" ) OVER ( PARTITION BY "Rando stuff" ) 
AS "Percentage"…

will solve this problem.
So you get a result like 0.5833 or 58.33%.

#5

because this is not another command to “Run”, but a setting for Base to ship the SQL to the database without checking it. (Quite useful, if a database offers the use of commands unknown to Base…)

#6

Awsome. I got soooo close. Thank you very much, RobertG. I have been down this rabbit hole for days. It is absorbing. One last thing, how would I format it as a percentage? Where would I stick the As “%”? I have put it some interesting places, but it has not done what I intended it too.

Thanks Wanderer, that makes so much sense. A bit earlier when I was merrily hitting buttons and going, But but but ? What? What?!!! It was very frustating, but now that it is working, sooooo much calmer.

I am such a convert from the spread sheet to the database now!

#7

Formatting a query works only for a moment. Click on the header of the column and with right mouse click choose “Column Format”.
You could switch the query to a view. The format for a view will be stored in the Base file.
Or you could create a form. There you choose a table control and format will also be saved.

#8

Fabulous. Thank you again.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.