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

Help a beginner with Query syntax?

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


Joined: 05 Sep 2006
Posts: 11

PostPosted: Sat Oct 14, 2006 12:59 am    Post subject: Help a beginner with Query syntax? Reply with quote

I am having a total block on this, and would be grateful for any help. I have a small database setup to record my staff's work, and am trying to figure out how to keep track of what's left to be done in each ward they're working on:


What I would like to do is find out how to get an "outstanding" figure by counting how many Records in "Respondents" have the WardID, and subtract that count from the "quota" field in ward. Every time I try to add a "count" function, I get a nasty error message that I don't really understand, something like this


I've tried right join, left join, inner join, but the real problem is that I clearly don't understand how to achieve my intended outcome. Can anyone help?
Back to top
View user's profile Send private message
audionuma
OOo Enthusiast
OOo Enthusiast


Joined: 23 Feb 2006
Posts: 126
Location: France

PostPosted: Sat Oct 14, 2006 4:12 am    Post subject: Reply with quote

hello,
the problem is that when you have a query with an agregate function (like COUNT() in your example), all others fields that are in the query outside of the agregate function must be in a GROUP BY clause.

For you, it would be like :

Code:
SELECT "respondents"."WardID", "wards"."quota", COUNT("respondents"."WardID") WHERE "respondents"."WardID" = "wards"."WardID" GROUP BY "respondents"."WardID", "wards"."quota"


although I don't know if the result will be what you expect.
_________________
OOo 2.3 / Mac OS PPC X.4.11
Back to top
View user's profile Send private message
DrewJensen
Super User
Super User


Joined: 06 Jul 2005
Posts: 2616
Location: Cumberland, MD

PostPosted: Sat Oct 14, 2006 4:32 am    Post subject: Reply with quote

Not sure I want to help on this...but suppose if I don't ask which party you are working for I can't be sorry for helping, right..LOL....just kidding.

I will use the QiQ feature of OOo2.0.4 for this -

First I want to get the total respondants per ward.

The SQL would be
SELECT COUNT( "RespondentID" ) AS "Total Respondents",
"WardID" FROM "Respondent" GROUP BY "WardID"

The query designer for this is of course:
Total Rrespondents

Then this value is used against the Ward table Quota field.

Thte SQL is:
SELECT *, "Quota"-"Total Respondents" AS "Outstanding" FROM "Ward",
(
SELECT COUNT( "RespondentID" ) AS "Total Respondents",
"WardID" FROM "Respondent" GROUP BY "WardID"
) AS "tmpTbl"
WHERE "Ward"."WardID" = "tmpTbl"."WardID"

Here is this in the Query Designer

Outstanding Quota

EDIT - Noticed that Audionuma had responded while I was working on my response. This is how it would look setup in the designer -

Single Query for values
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Vernon Compton
General User
General User


Joined: 05 Sep 2006
Posts: 11

PostPosted: Sat Oct 14, 2006 11:47 am    Post subject: Reply with quote

Thank you both very much for clearing up both the particular problem and explaining how it works. Oh, and Drew, you can relax, this is for a research company, surveying residents' satisfaction with council performance. The client is the council itself, and the research is strictly apolitical. Very Happy
Back to top
View user's profile Send private message
DrewJensen
Super User
Super User


Joined: 06 Jul 2005
Posts: 2616
Location: Cumberland, MD

PostPosted: Sat Oct 14, 2006 11:57 am    Post subject: Reply with quote

Oh, good I was afraid you were representing THAT OTHER political party - course I am not saying which one that is.. Shocked
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Vernon Compton
General User
General User


Joined: 05 Sep 2006
Posts: 11

PostPosted: Sat Oct 14, 2006 3:08 pm    Post subject: Reply with quote

If I might push my luck a bit further:

Thanks to the help in this thread, I've managed to create queries to give me tallies of respondents by staffID and age/gender groupings. I've also managed to create the QiQ to give the balance outstanding for one group, M40+. What I'm wondering is if I can "daisy chain the queries for each age/gender group.

Here's the first QiQ I made:


What I'm wondering is how to "daisy chain" this sort of query. I've tried adding in another one, like this:


but as the screenshot shows, it doesn't actually work. Can I daisy chain like this, to get all the quotas and "outstanding" sums in one query? Also, if that is possible, can I order the columns, so that, for example, "M18-39quota" is adjjacent to "m<40 left", etc.?
Back to top
View user's profile Send private message
Vernon Compton
General User
General User


Joined: 05 Sep 2006
Posts: 11

PostPosted: Mon Oct 16, 2006 1:28 pm    Post subject: Reply with quote

I'm just bumping this up since I'd love to know if I can concatenate queries as I'm trying to or not.
Back to top
View user's profile Send private message
tuari hemi
Newbie
Newbie


Joined: 17 Oct 2006
Posts: 1

PostPosted: Tue Oct 17, 2006 12:34 pm    Post subject: Reply with quote

Vernon Compton wrote:
I'm just bumping this up since I'd love to know if I can concatenate queries as I'm trying to or not.


I am actually wondering something similar myself, how to string queries together. I'd say that it's probably not possible, Vernon, since no one's replied to your question. Which would be a pity.
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