OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Using COUNTIF() on strings

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
K-Rad
General User
General User


Joined: 26 Jun 2012
Posts: 8

PostPosted: Wed Jun 27, 2012 5:46 pm    Post subject: Using COUNTIF() on strings Reply with quote

Hi guys, before I get going I just wanted to let you know that this thread isn't a duplicate or mistake, it is just an extension of a problem which Sliderule graciously assisted me with earlier.

My problem is this: I have a query which needs to find a bunch of totals, grouping together some things to find a sub-total, and to find a grand total. Mostly these are simple numerical values that come in under one field each. Sliderule has helped me understand how this is achieved but the spanner gets thrown into the works when I have a final option available for what is essentially a catchall for all other potential options.

The people using the database can enter in a numerical value for products: VP1, VP2, VP3, VP7, VP8, VP9, ClassGrp however they can also enter in a number for 'GRPother' and (due to the almost limitless options available that come under the GRPother label) a text description of what this GRPother actually is in 'GRPotherDesc'. To further complicate the matter, there can be more than 1 type of 'GRPother', for example:
You can put 'GRPother' as '2', and 'GRPotherDesc' as 'choice, band'.

What I used to do with Calc was use sort them into a single column (as I'd input one text description per cell) and run a COUNTIF. I am unsure how to make this work with the current code:
Code:

SELECT
            "YearClass" AS "Year & Class",
            SUM( COALESCE( "ClassGrp", 0 ) ) AS "Laminated Class Photo",
            SUM( COALESCE( "VP1", 0 ) + COALESCE( "VP2", 0 ) + COALESCE( "VP3", 0)      + COALESCE( "VP7", 0 ) + COALESCE( "VP8", 0 ) + COALESCE( "VP9", 0 ) + COALESCE( "ClassGrp", 0 ) ) AS "Group Total"

FROM "StudentInfo"

GROUP BY "YearClass"

HAVING SUM( COALESCE( "VP1", 0 ) + COALESCE( "VP2", 0 ) + COALESCE( "VP3", 0 ) + COALESCE( "VP7", 0 ) + COALESCE( "VP8", 0 ) + COALESCE( "VP9", 0 ) + COALESCE( "ClassGrp", 0 ) ) > 0

ORDER BY "YearClass" ASC


As always, assistance is greatly appreciated! When I searched the forums for examples it seems to only have dealt with numbers in different fields as opposed to text in the one.

Am I approaching this wrong and should be including extra text fields, such as 'GRPotherDesc1', 'GRPotherDesc2', 'GRPotherDesc3' and just removing the 'GRPother' numerical altogether?
Back to top
View user's profile Send private message
K-Rad
General User
General User


Joined: 26 Jun 2012
Posts: 8

PostPosted: Mon Jul 02, 2012 6:07 pm    Post subject: Reply with quote

After some more thought, I figured it might be easier to give multiple input fields for 'GRPother' instead of having, for example, 10 different groups input in the one field and separated by a comma.

So now instead of 'GRPother' and 'GRPotherDesc' I have 'GrpOth1' through 'GrpOther8'.

What I still don't understand is how I can program the query to list the different string values input in the 'GrpOth' fields and a count of how many instances of each one there are.

Help would be greatly appreciated!
_________________
What a tragic waste of potential.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group