[Solved] How to display percentage in query

Creating tables and queries

[Solved] How to display percentage in query

Postby gkick » Tue Sep 17, 2019 9:52 pm

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.
Last edited by robleyd on Sat Sep 21, 2019 4:59 am, edited 3 times in total.
Reason: Add green tick
Libre Office 6.2 on Windows 10 HSQL 2.5 backend
gkick
 
Posts: 192
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

Re: How to display percentage in query

Postby robleyd » Wed Sep 18, 2019 2:17 am

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.
Cheers
David
Apache OpenOffice 420m2(Build:9821) - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 3145
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: How to display percentage in query

Postby Sliderule » Wed Sep 18, 2019 4:41 pm

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.
User avatar
Sliderule
Volunteer
 
Posts: 1207
Joined: Thu Nov 29, 2007 9:46 am

Re: How to display percentage in query

Postby gkick » Thu Sep 19, 2019 7:46 am

@ 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
Attachments
Vertical.JPG
SideBySide.JPG
Libre Office 6.2 on Windows 10 HSQL 2.5 backend
gkick
 
Posts: 192
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

Re: How to display percentage in query

Postby Villeroy » Thu Sep 19, 2019 9:49 am

This is exactly the type of report that can be produced with a pivot table on a Calc sheet.
Attachments
pivot_99369.ods
Dummy db with 3 pivots
(22.74 KiB) Downloaded 21 times
Last edited by Villeroy on Thu Sep 19, 2019 3:22 pm, edited 1 time in total.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27760
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How to display percentage in query

Postby Sliderule » Thu Sep 19, 2019 3:00 pm

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.
User avatar
Sliderule
Volunteer
 
Posts: 1207
Joined: Thu Nov 29, 2007 9:46 am

Re: How to display percentage in query

Postby Sliderule » Thu Sep 19, 2019 7:03 pm

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.
User avatar
Sliderule
Volunteer
 
Posts: 1207
Joined: Thu Nov 29, 2007 9:46 am

Re: How to display percentage in query

Postby gkick » Fri Sep 20, 2019 4:31 am

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.
Libre Office 6.2 on Windows 10 HSQL 2.5 backend
gkick
 
Posts: 192
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

Re: How to display percentage in query

Postby Sliderule » Fri Sep 20, 2019 4:53 am

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.
User avatar
Sliderule
Volunteer
 
Posts: 1207
Joined: Thu Nov 29, 2007 9:46 am

Re: How to display percentage in query

Postby gkick » Fri Sep 20, 2019 6:57 am

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
Attachments
oops.JPG
Libre Office 6.2 on Windows 10 HSQL 2.5 backend
gkick
 
Posts: 192
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

Re: How to display percentage in query

Postby Sliderule » Fri Sep 20, 2019 3:28 pm

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.
User avatar
Sliderule
Volunteer
 
Posts: 1207
Joined: Thu Nov 29, 2007 9:46 am

Re: How to display percentage in query

Postby gkick » Sat Sep 21, 2019 4:16 am

Hurra, the last one works ! Thanks a lot

cheers

GK
Libre Office 6.2 on Windows 10 HSQL 2.5 backend
gkick
 
Posts: 192
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

Re: [Solved] How to display percentage in query

Postby Sliderule » Sun Sep 22, 2019 3:58 pm

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
User avatar
Sliderule
Volunteer
 
Posts: 1207
Joined: Thu Nov 29, 2007 9:46 am

Re: [Solved] How to display percentage in query

Postby gkick » Mon Sep 23, 2019 4:50 pm

Thank you!
Libre Office 6.2 on Windows 10 HSQL 2.5 backend
gkick
 
Posts: 192
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

Re: [Solved] How to display percentage in query

Postby gkick » Mon Sep 23, 2019 4:53 pm

Thank you!
Libre Office 6.2 on Windows 10 HSQL 2.5 backend
gkick
 
Posts: 192
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 0 guests