[Solved] How to display percentage in query

[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

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

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.

[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.

[Sliderule]

Code: Select all

-- 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.

[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

[Villeroy]

This is exactly the type of report that can be produced with a pivot table on a Calc sheet.

[Sliderule]

@ 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:

viewtopic.php?f=61&t=99164&p=476711&hil ... n+#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.

[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

-- 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

-- 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.

[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

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.

[Sliderule]

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

CAST(COUNT(AGE("tblContacts"."MY_INTEGER_COLUMN")) as FLOAT) as "SUM_TOTAL"

to:

Code: Select all

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.

[gkick]

Ok, here is the latest

Code: Select all

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

[Sliderule]

gkick:

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

Run this Query.

Code: Select all

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:

Change:

Code: Select all

CAST(COUNT(AGE("tblContacts"."MY_INTEGER_COLUMN")) as FLOAT) as "SUM_TOTAL"

to:

Code: Select all

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.

[gkick]

Hurra, the last one works ! Thanks a lot

cheers

GK

[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

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

[gkick]

Thank you!

[gkick]

Thank you!