[Solved] Getting total number of a code in a field

Getting your data onto paper - or the web - Discussing the reports features of Base

[Solved] Getting total number of a code in a field

Postby tomwatson » Fri Jun 20, 2008 2:38 pm

I need to generate reports to show the total number of entrys where a field has a specific vaulue. For example if I have a hatabase holding cstomer details and a field called gender. 6 of my customers are male and 4 are female. I want a report to show this like this:-

Male - 6
Female - 4

All I can get it to do is

Male
Male
Male
Male
Male
male
Female
Female
Female
Female
and have me count it manually which is not acceptable if a databse has over 1000 entries in it.

Any help would be appreciated.
Last edited by Hagar Delest on Fri Jun 20, 2008 4:46 pm, edited 1 time in total.
Reason: tagged the thread as Solved.
tomwatson
 
Posts: 6
Joined: Wed May 07, 2008 10:41 am

Re: Getting total number of a code in a field

Postby DrewJensen » Fri Jun 20, 2008 2:47 pm

You can use a query to give you this information.

If you table is named Table1 and you have a column named Gender then:

SELECT "Gender", Count( "Gender" ) AS "CntGndr" FROM "Table1" GROUP BY "Gender"

The result of your query will be 2 rows with 2 columns.

If this is all you are wanting to show in your report then just use the query as the basis for your report.
Former member of The Document Foundation
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
User avatar
DrewJensen
Volunteer
 
Posts: 1734
Joined: Sat Oct 06, 2007 9:01 pm
Location: Cumberland, MD - USA

Re: Getting total number of a code in a field

Postby tomwatson » Fri Jun 20, 2008 4:23 pm

Thanks.

That solves my problem.
tomwatson
 
Posts: 6
Joined: Wed May 07, 2008 10:41 am

Re: [Solved] Getting total number of a code in a field

Postby Zeedok » Mon Sep 01, 2008 2:23 am

I have used this solution - successfully - but I need some help with a minor complication.

I have an 'Outcome' filed in my table with four possible values (eg Stable, Improved, Deteriorated etc). By shamelessly 'copy/paste' I have created this SQL query:

SELECT "Outcome", COUNT( "Outcome" ) AS "CntOutcome" FROM "New mega table" GROUP BY "Outcome"

Which works fine, but . . . I would like to exclude some patients from the count - those that have not received a particular treatment - which I have as a YES/NO value in another field in the same table.

I would also like a Total count (ie all of the patients) just to make sure nothing is duplicated or missing.

Is this possible? How so?

Thanks - great forum by the way, have already found it very useful.
OOo 2.4.X on Ubuntu 8.x + Vista
Zeedok
 
Posts: 10
Joined: Mon Sep 01, 2008 2:03 am


Return to Reporting

Who is online

Users browsing this forum: No registered users and 1 guest