Page 1 of 1

[Solved] How to display percentage in query

PostPosted: Tue Sep 17, 2019 9:52 pm
by gkick
Hi all,

How does one incorporate percentages in aggregate function or sub query
For some demographic data I use a standard aggregate query
Code: Select all   Expand viewCollapse view
SELECT "ContactType", COUNT( "ContactType" ) FROM "ViewContactsAll" GROUP BY "ContactType" ORDER BY "ContactType" ASC


which returns number of friends, number of professionals, number of family members etc
for this as well as my age distribution query I would like to show a percentage next to the count, but have no idea of how to go about it.
This is the age distribution query

Code: Select all   Expand viewCollapse view
SELECT t.age_group, COUNT(*) AS age_count
FROM
(
    SELECT
        CASE WHEN DATEDIFF(YEAR, "DateOfBirth", CURDATE()) BETWEEN 1 AND 10
             THEN '1-10'
          WHEN DATEDIFF(YEAR, "DateOfBirth", CURDATE()) BETWEEN 11 AND 20
             THEN '11-20'
             WHEN DATEDIFF(YEAR, "DateOfBirth", CURDATE()) BETWEEN 21 AND 30
             THEN '21-30'
             WHEN DATEDIFF(YEAR, "DateOfBirth", CURDATE()) BETWEEN 31 AND 40
             THEN '31-40'
             WHEN DATEDIFF(YEAR, "DateOfBirth", CURDATE()) BETWEEN 41 AND 50
             THEN '41-50'
          WHEN DATEDIFF(YEAR, "DateOfBirth", CURDATE()) BETWEEN 51 AND 60
             THEN '51-60'
          WHEN DATEDIFF(YEAR, "DateOfBirth", CURDATE()) BETWEEN 51 AND 61
             THEN '61-70'
             WHEN DATEDIFF(YEAR, "DateOfBirth", CURDATE()) > 70
             THEN '71-plus'
             ELSE 'No Info'
        END AS age_group
    FROM "tblContacts"
) t
GROUP BY t.age_group ORDER BY t.age_group


Any pointers would be very much appreciated.

Re: How to display percentage in query

PostPosted: Wed Sep 18, 2019 2:17 am
by robleyd
You might find this topic helpful.

Or try a forum search for percentage query in the Base section of the forum - it may provide some other useful topics.

Re: How to display percentage in query

PostPosted: Wed Sep 18, 2019 4:41 pm
by Sliderule
Code: Select all   Expand viewCollapse view
-- Sample Query Below will return results I think you are looking for.
-- TWO important thing: 1. This is for a table I have and it works, you have to translate it for your table
--                      2. Because of Base Parser, MUST be run Direct ( sent directly to HSQL ) 
Select
   A."COUNT_1_70",
   A."COUNT_1_70" / A."COUNT_TOTAL" as "PCT_1_70",
   A."COUNT_71_150",
   A."COUNT_71_150" / A."COUNT_TOTAL"  as "PCT_71_150",
   A."COUNT_151_250",
   A."COUNT_151_250" / A."COUNT_TOTAL" as "PCT_151_250",
   A."COUNT_>_250",
   A."COUNT_>_250" / A."COUNT_TOTAL" as "PCT_>_250",
   A."COUNT_TOTAL"
From (
      Select
         CAST(COUNT(CASEWHEN("MY_TABLE"."MY_INTEGER_COLUMN" Between 1 and 70, '1_70', NULL)) as FLOAT) as "COUNT_1_70",
         CAST(COUNT(CASEWHEN("MY_TABLE"."MY_INTEGER_COLUMN" Between 71 and 150, '71_150', NULL)) as FLOAT) as "COUNT_71_150",
         CAST(COUNT(CASEWHEN("MY_TABLE"."MY_INTEGER_COLUMN" Between 151 and 250, '151_250', NULL)) as FLOAT) as "COUNT_151_250",
         CAST(COUNT(CASEWHEN("MY_TABLE"."MY_INTEGER_COLUMN" > 250, '>_250', NULL)) as FLOAT) as "COUNT_>_250",
         CAST(COUNT("MY_TABLE"."MY_INTEGER_COLUMN") as FLOAT) as "COUNT_TOTAL"
      From "MY_TABLE"
      Where "MY_TABLE"."MY_DATE_COLUMN" Between '1999-12-28' and '2000-03-05'
       ) as A


I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

Re: How to display percentage in query

PostPosted: Thu Sep 19, 2019 7:46 am
by gkick
@ Robleyd - thanks for the links, did do some research before but only found posts relating to percentages as calculated fields

@ Sliderule - thanks, almost there ! Is your MY_Integer_Column containing the age of a person?
Because if I use my DateOfBirth column I end up with an incompatible datatype message, if however I use another integer column I get this as below and I can use the horizontal format for something else.

But what I really want to achieve is to have the % calcs running down to the right of the age group
Thks

Re: How to display percentage in query

PostPosted: Thu Sep 19, 2019 9:49 am
by Villeroy
This is exactly the type of report that can be produced with a pivot table on a Calc sheet.

Re: How to display percentage in query

PostPosted: Thu Sep 19, 2019 3:00 pm
by Sliderule
GK wrote:
@ Sliderule - thanks, almost there ! Is your MY_Integer_Column containing the age of a person?
Because if I use my DateOfBirth column I end up with an incompatible datatype message, if however I use another integer column I get this as below and I can use the horizontal format for something else.

Yes, it is an Integer, just as the CREATE FUNCTION AGE as defined in your / my previous forum post:

https://forum.openoffice.org/en/forum/viewtopic.php?f=61&t=99164&p=476711&hilit=age+create+function+#p476711

as it returns an Integer ( different than CREATE FUNCTION AGE_FLOAT that returns a Float . . . and unlike using DATEDIFF, will not return age calculations.

Unless you return your actual SQL Query ( words, the Select statement, not a picture ), I have no idea what the problem is.

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

Re: How to display percentage in query

PostPosted: Thu Sep 19, 2019 7:03 pm
by Sliderule
Another way of doing it . . . based on the ABOVE Query I gave you, plus, another Query using AGE ( returning INTEGER ) User Defined Query.

Code: Select all   Expand viewCollapse view
-- Sample Query Below will return results I think you are looking for.
-- TWO important thing: 1. This is for a table I have and it works, you have to translate it for your table
--                      2. This may be WITH Base Parser, or, Direct with HSQL
Select
   A."SUM_1_70",
   A."SUM_1_70" / A."SUM_TOTAL" as "PCT_1_70",
   A."SUM_71_150",
   A."SUM_71_150" / A."SUM_TOTAL"  as "PCT_71_150",
   A."SUM_151_250",
   A."SUM_151_250" / A."SUM_TOTAL" as "PCT_151_250",
   A."SUM_>_250",
   A."SUM_>_250" / A."SUM_TOTAL" as "PCT_>_250",
   A."SUM_TOTAL"
From (
      Select
         CAST(SUM(CASE WHEN "MY_TABLE"."MY_INTEGER_COLUMN" Between 1 and 70 THEN 1 ELSE 0 END) as FLOAT) as "SUM_1_70",
         CAST(SUM(CASE WHEN "MY_TABLE"."MY_INTEGER_COLUMN" Between 71 and 150 THEN 1 ELSE 0 END) as FLOAT) as "SUM_71_150",
         CAST(SUM(CASE WHEN "MY_TABLE"."MY_INTEGER_COLUMN" Between 151 and 250 THEN 1 ELSE 0 END) as FLOAT) as "SUM_151_250",
         CAST(SUM(CASE WHEN "MY_TABLE"."MY_INTEGER_COLUMN" > 250 THEN 1 ELSE 0 END) as FLOAT) as "SUM_>_250",
         CAST(COUNT("MY_TABLE"."MY_INTEGER_COLUMN") as FLOAT) as "SUM_TOTAL"
      From "MY_TABLE"
      Where "MY_TABLE"."MY_DATE_COLUMN" Between '1999-12-28' and '2000-03-05'
       ) as A


Code: Select all   Expand viewCollapse view
-- Sample Query Below will return results I think you are looking for.
-- THREE important thing: 1. Change the TABLE and COLUMN names to match your database
--                        2. This may be WITH Base Parser, or, Direct
--                        3. Uses AGE function ( returning INTEGER) - AGE("MY_TABLE"."BIRTH_DATE", CURRENT_DATE)
Select
   A."SUM_1_10",
   A."SUM_1_10" / A."SUM_TOTAL" as "PCT_1_10",
   A."SUM_11_20",
   A."SUM_11_20" / A."SUM_TOTAL"  as "PCT_11_20",
   A."SUM_21_30",
   A."SUM_21_30" / A."SUM_TOTAL" as "PCT_21_30",
   A."SUM_31_40",
   A."SUM_31_40" / A."SUM_TOTAL" as "PCT_31_40",
   A."SUM_41_50",
   A."SUM_41_50" / A."SUM_TOTAL" as "PCT_41_50",
   A."SUM_51_60",
   A."SUM_51_60" / A."SUM_TOTAL" as "PCT_51_60",
   A."SUM_61_70",
   A."SUM_61_70" / A."SUM_TOTAL" as "PCT_61_70",
   A."SUM_>_70",
   A."SUM_>_70" / A."SUM_TOTAL" as "PCT_>_70",
   A."SUM_TOTAL"
From (
      Select
         CAST(SUM(CASE WHEN AGE("MY_TABLE"."BIRTH_DATE", CURRENT_DATE) Between  1 and 10 THEN 1 ELSE 0 END) as FLOAT) as "SUM_1_10",
         CAST(SUM(CASE WHEN AGE("MY_TABLE"."BIRTH_DATE", CURRENT_DATE) Between 11 and 20 THEN 1 ELSE 0 END) as FLOAT) as "SUM_11_20",
         CAST(SUM(CASE WHEN AGE("MY_TABLE"."BIRTH_DATE", CURRENT_DATE) Between 21 and 30 THEN 1 ELSE 0 END) as FLOAT) as "SUM_21_30",
         CAST(SUM(CASE WHEN AGE("MY_TABLE"."BIRTH_DATE", CURRENT_DATE) Between 31 and 40 THEN 1 ELSE 0 END) as FLOAT) as "SUM_31_40",
         CAST(SUM(CASE WHEN AGE("MY_TABLE"."BIRTH_DATE", CURRENT_DATE) Between 41 and 50 THEN 1 ELSE 0 END) as FLOAT) as "SUM_41_50",
         CAST(SUM(CASE WHEN AGE("MY_TABLE"."BIRTH_DATE", CURRENT_DATE) Between 51 and 60 THEN 1 ELSE 0 END) as FLOAT) as "SUM_51_60",
         CAST(SUM(CASE WHEN AGE("MY_TABLE"."BIRTH_DATE", CURRENT_DATE) Between 61 and 70 THEN 1 ELSE 0 END) as FLOAT) as "SUM_61_70",
         CAST(SUM(CASE WHEN AGE("MY_TABLE"."BIRTH_DATE", CURRENT_DATE) > 71 THEN 1 ELSE 0 END) as FLOAT) as "SUM_>_70",
         CAST(COUNT(AGE("MY_TABLE"."MY_INTEGER_COLUMN")) as FLOAT) as "SUM_TOTAL"
      From "MY_TABLE"
      Where "MY_TABLE"."MY_DATE_COLUMN" Between '1999-12-28' and '2000-03-05'
       ) as A

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

Re: How to display percentage in query

PostPosted: Fri Sep 20, 2019 4:31 am
by gkick
Thanks all, tried to do a quick mockup then realising the embedded thing does not support datediff..

Yes Villeroy I agree, pivots tables and pivot charts are wonderful, used them a lot some 15 years ago, think Access 2000 or 2002 introduced that functionality.
Yet I prefer to have that info table within a form rather then having to switch back and forth and I can understand the preference to do report out of CALC.

Sliderule, thanks again but maybe I am stupid, turning senil, I just can not figure out the MY-Integer bit at the end

Code: Select all   Expand viewCollapse view
Select
         CAST(SUM(CASE WHEN AGE("tblContacts"."DateOfBirth", CURRENT_DATE) Between  1 and 10 THEN 1 ELSE 0 END) as FLOAT) as "SUM_1_10",
         CAST(SUM(CASE WHEN AGE("tblContacts"."DateOfBirth", CURRENT_DATE) Between 11 and 20 THEN 1 ELSE 0 END) as FLOAT) as "SUM_11_20",
         CAST(SUM(CASE WHEN AGE("tblContacts"."DateOfBirth", CURRENT_DATE) Between 21 and 30 THEN 1 ELSE 0 END) as FLOAT) as "SUM_21_30",
         CAST(SUM(CASE WHEN AGE("tblContacts"."DateOfBirth", CURRENT_DATE) Between 31 and 40 THEN 1 ELSE 0 END) as FLOAT) as "SUM_31_40",
         CAST(SUM(CASE WHEN AGE("tblContacts"."DateOfBirth", CURRENT_DATE) Between 41 and 50 THEN 1 ELSE 0 END) as FLOAT) as "SUM_41_50",
         CAST(SUM(CASE WHEN AGE("tblContacts"."DateOfBirth", CURRENT_DATE) Between 51 and 60 THEN 1 ELSE 0 END) as FLOAT) as "SUM_51_60",
         CAST(SUM(CASE WHEN AGE("tblContacts"."DateOfBirth", CURRENT_DATE) Between 61 and 70 THEN 1 ELSE 0 END) as FLOAT) as "SUM_61_70",
         CAST(SUM(CASE WHEN AGE("tblContacts"."DateOfBirth", CURRENT_DATE) > 71 THEN 1 ELSE 0 END) as FLOAT) as "SUM_>_70",
         CAST(COUNT(AGE("tblContacts"."MY_INTEGER_COLUMN")) as FLOAT) as "SUM_TOTAL"
      From "tblContacts"
      Where "tblContacts"."DateOfBirth" Between '1999-12-28' and '2000-03-05'
       ) as A


Anyway I will play a little more. And thanks again folks.

Re: How to display percentage in query

PostPosted: Fri Sep 20, 2019 4:53 am
by Sliderule
gkick wrote:Sliderule, thanks again but maybe I am stupid, turning senil, I just can not figure out the MY-Integer bit at the end

You did NOT post the entire Query, only a part.

Change:

Code: Select all   Expand viewCollapse view
CAST(COUNT(AGE("tblContacts"."MY_INTEGER_COLUMN")) as FLOAT) as "SUM_TOTAL"


to:

Code: Select all   Expand viewCollapse view
CAST(COUNT("tblContacts"."DateOfBirth") as FLOAT) as "SUM_TOTAL"


¿ Additionally, do you really want / need the Where clause, I doubt it, but, if you need a Where clause, that is where you would put it ?

Once you get it work as you want, PLEASE post your final working Query here, so, others can learn from it.

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

Re: How to display percentage in query

PostPosted: Fri Sep 20, 2019 6:57 am
by gkick
Ok, here is the latest

Code: Select all   Expand viewCollapse view
Select
   A."SUM_1_10",
   A."SUM_1_10" / A."SUM_TOTAL" as "PCT_1_10",
   A."SUM_11_20",
   A."SUM_11_20" / A."SUM_TOTAL"  as "PCT_11_20",
   A."SUM_21_30",
   A."SUM_21_30" / A."SUM_TOTAL" as "PCT_21_30",
   A."SUM_31_40",
   A."SUM_31_40" / A."SUM_TOTAL" as "PCT_31_40",
   A."SUM_41_50",
   A."SUM_41_50" / A."SUM_TOTAL" as "PCT_41_50",
   A."SUM_51_60",
   A."SUM_51_60" / A."SUM_TOTAL" as "PCT_51_60",
   A."SUM_61_70",
   A."SUM_61_70" / A."SUM_TOTAL" as "PCT_61_70",
   A."SUM_>_70",
   A."SUM_>_70" / A."SUM_TOTAL" as "PCT_>_70",
   A."SUM_TOTAL"
From (
      Select
         CAST(SUM(CASE WHEN AGE("tblContacts"."DateOfBirth", CURRENT_DATE) Between  1 and 10 THEN 1 ELSE 0 END) as FLOAT) as "SUM_1_10",
         CAST(SUM(CASE WHEN AGE("tblContacts"."DateOfBirth", CURRENT_DATE) Between 11 and 20 THEN 1 ELSE 0 END) as FLOAT) as "SUM_11_20",
         CAST(SUM(CASE WHEN AGE("tblContacts"."DateOfBirth", CURRENT_DATE) Between 21 and 30 THEN 1 ELSE 0 END) as FLOAT) as "SUM_21_30",
         CAST(SUM(CASE WHEN AGE("tblContacts"."DateOfBirth", CURRENT_DATE) Between 31 and 40 THEN 1 ELSE 0 END) as FLOAT) as "SUM_31_40",
         CAST(SUM(CASE WHEN AGE("tblContacts"."DateOfBirth", CURRENT_DATE) Between 41 and 50 THEN 1 ELSE 0 END) as FLOAT) as "SUM_41_50",
         CAST(SUM(CASE WHEN AGE("tblContacts"."DateOfBirth", CURRENT_DATE) Between 51 and 60 THEN 1 ELSE 0 END) as FLOAT) as "SUM_51_60",
         CAST(SUM(CASE WHEN AGE("tblContacts"."DateOfBirth", CURRENT_DATE) Between 61 and 70 THEN 1 ELSE 0 END) as FLOAT) as "SUM_61_70",
         CAST(SUM(CASE WHEN AGE("tblContacts"."DateOfBirth", CURRENT_DATE) > 71 THEN 1 ELSE 0 END) as FLOAT) as "SUM_>_70",
         CAST(COUNT(AGE("tblContacts"."DateOfBirth")) as FLOAT) as "SUM_TOTAL"
      From "tblContacts"
      Where "tblContacts"."DateOfBirth" Between '1999-12-28' and '2000-03-05'
       ) as A


producing this which is weird because both the AGE as well as the AGE_FLOAT functions are working in another part of the form

Re: How to display percentage in query

PostPosted: Fri Sep 20, 2019 3:28 pm
by Sliderule
gkick:

You did NOT follow my instructions in my post immediately above this one.

Run this Query.

Code: Select all   Expand viewCollapse view
Select
   A."SUM_1_10",
   A."SUM_1_10" / A."SUM_TOTAL" as "PCT_1_10",
   A."SUM_11_20",
   A."SUM_11_20" / A."SUM_TOTAL"  as "PCT_11_20",
   A."SUM_21_30",
   A."SUM_21_30" / A."SUM_TOTAL" as "PCT_21_30",
   A."SUM_31_40",
   A."SUM_31_40" / A."SUM_TOTAL" as "PCT_31_40",
   A."SUM_41_50",
   A."SUM_41_50" / A."SUM_TOTAL" as "PCT_41_50",
   A."SUM_51_60",
   A."SUM_51_60" / A."SUM_TOTAL" as "PCT_51_60",
   A."SUM_61_70",
   A."SUM_61_70" / A."SUM_TOTAL" as "PCT_61_70",
   A."SUM_>_70",
   A."SUM_>_70" / A."SUM_TOTAL" as "PCT_>_70",
   A."SUM_TOTAL"
From (
      Select
         CAST(SUM(CASE WHEN AGE("tblContacts"."DateOfBirth", CURRENT_DATE) Between  1 and 10 THEN 1 ELSE 0 END) as FLOAT) as "SUM_1_10",
         CAST(SUM(CASE WHEN AGE("tblContacts"."DateOfBirth", CURRENT_DATE) Between 11 and 20 THEN 1 ELSE 0 END) as FLOAT) as "SUM_11_20",
         CAST(SUM(CASE WHEN AGE("tblContacts"."DateOfBirth", CURRENT_DATE) Between 21 and 30 THEN 1 ELSE 0 END) as FLOAT) as "SUM_21_30",
         CAST(SUM(CASE WHEN AGE("tblContacts"."DateOfBirth", CURRENT_DATE) Between 31 and 40 THEN 1 ELSE 0 END) as FLOAT) as "SUM_31_40",
         CAST(SUM(CASE WHEN AGE("tblContacts"."DateOfBirth", CURRENT_DATE) Between 41 and 50 THEN 1 ELSE 0 END) as FLOAT) as "SUM_41_50",
         CAST(SUM(CASE WHEN AGE("tblContacts"."DateOfBirth", CURRENT_DATE) Between 51 and 60 THEN 1 ELSE 0 END) as FLOAT) as "SUM_51_60",
         CAST(SUM(CASE WHEN AGE("tblContacts"."DateOfBirth", CURRENT_DATE) Between 61 and 70 THEN 1 ELSE 0 END) as FLOAT) as "SUM_61_70",
         CAST(SUM(CASE WHEN AGE("tblContacts"."DateOfBirth", CURRENT_DATE) > 70 THEN 1 ELSE 0 END) as FLOAT) as "SUM_>_70",
         CAST(COUNT("tblContacts"."DateOfBirth") as FLOAT) as "SUM_TOTAL"
      From "tblContacts"
       ) as A


Explanation:
Sliderule wrote:
Change:

Code: Select all   Expand viewCollapse view
CAST(COUNT(AGE("tblContacts"."MY_INTEGER_COLUMN")) as FLOAT) as "SUM_TOTAL"

to:

Code: Select all   Expand viewCollapse view
CAST(COUNT("tblContacts"."DateOfBirth") as FLOAT) as "SUM_TOTAL"


¿ Additionally, do you really want / need the Where clause, I doubt it, but, if you need a Where clause, that is where you would put it ?

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

Re: How to display percentage in query

PostPosted: Sat Sep 21, 2019 4:16 am
by gkick
Hurra, the last one works ! Thanks a lot

cheers

GK

Re: [Solved] How to display percentage in query

PostPosted: Sun Sep 22, 2019 3:58 pm
by Sliderule
Just as an FYI ( For Your Information ), an easier way to write, visualize, understand and maintain the SQL Query, would be:

Code: Select all   Expand viewCollapse view
Select
   CAST(SUM(CASE WHEN AGE("tblContacts"."DateOfBirth", CURRENT_DATE) Between 1 and 10 THEN 1 ELSE 0 END) as FLOAT) as "COUNT_1_10",
   CAST(SUM(CASE WHEN AGE("tblContacts"."DateOfBirth", CURRENT_DATE) Between 1 and 10 THEN 1 ELSE 0 END) as FLOAT) / COUNT(*) as "PCT_1_10",
   CAST(SUM(CASE WHEN AGE("tblContacts"."DateOfBirth", CURRENT_DATE) Between 11 and 20 THEN 1 ELSE 0 END) as FLOAT) as "COUNT_11_20",
   CAST(SUM(CASE WHEN AGE("tblContacts"."DateOfBirth", CURRENT_DATE) Between 11 and 20 THEN 1 ELSE 0 END) as FLOAT) / COUNT(*) as "PCT_11_20",
   CAST(SUM(CASE WHEN AGE("tblContacts"."DateOfBirth", CURRENT_DATE) Between 21 and 30 THEN 1 ELSE 0 END) as FLOAT) as "COUNT_21_30",
   CAST(SUM(CASE WHEN AGE("tblContacts"."DateOfBirth", CURRENT_DATE) Between 21 and 30 THEN 1 ELSE 0 END) as FLOAT) / COUNT(*) as "PCT_21_30",
   CAST(SUM(CASE WHEN AGE("tblContacts"."DateOfBirth", CURRENT_DATE) Between 31 and 40 THEN 1 ELSE 0 END) as FLOAT) as "COUNT_31_40",
   CAST(SUM(CASE WHEN AGE("tblContacts"."DateOfBirth", CURRENT_DATE) Between 31 and 40 THEN 1 ELSE 0 END) as FLOAT) / COUNT(*) as "PCT_31_40",
   CAST(SUM(CASE WHEN AGE("tblContacts"."DateOfBirth", CURRENT_DATE) Between 41 and 50 THEN 1 ELSE 0 END) as FLOAT) as  "COUNT_41_50",
   CAST(SUM(CASE WHEN AGE("tblContacts"."DateOfBirth", CURRENT_DATE) Between 41 and 50 THEN 1 ELSE 0 END) as FLOAT) / COUNT(*) as "PCT_41_50",
   CAST(SUM(CASE WHEN AGE("tblContacts"."DateOfBirth", CURRENT_DATE) Between 51 and 60 THEN 1 ELSE 0 END) as FLOAT) as "COUNT_51_60",
   CAST(SUM(CASE WHEN AGE("tblContacts"."DateOfBirth", CURRENT_DATE) Between 51 and 60 THEN 1 ELSE 0 END) as FLOAT) / COUNT(*) as "PCT_51_60",
   CAST(SUM(CASE WHEN AGE("tblContacts"."DateOfBirth", CURRENT_DATE) Between 61 and 70 THEN 1 ELSE 0 END) as FLOAT) as "COUNT_61_70",
   CAST(SUM(CASE WHEN AGE("tblContacts"."DateOfBirth", CURRENT_DATE) Between 61 and 70 THEN 1 ELSE 0 END) as FLOAT) / COUNT(*) as "PCT_61_70",
   CAST(SUM(CASE WHEN AGE("tblContacts"."DateOfBirth", CURRENT_DATE) > 70 THEN 1 ELSE 0 END) as FLOAT) as "COUNT_>_70",
   CAST(SUM(CASE WHEN AGE("tblContacts"."DateOfBirth", CURRENT_DATE) > 70 THEN 1 ELSE 0 END) as FLOAT) / COUNT(*) as "PCT_>_70",
   COUNT(*) as "COUNT_TOTAL"
From "tblContacts"

Sliderule

Re: [Solved] How to display percentage in query

PostPosted: Mon Sep 23, 2019 4:50 pm
by gkick
Thank you!

Re: [Solved] How to display percentage in query

PostPosted: Mon Sep 23, 2019 4:53 pm
by gkick
Thank you!