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

SQL Count Help

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


Joined: 20 Jun 2005
Posts: 23
Location: Brazil

PostPosted: Mon Aug 22, 2011 6:57 am    Post subject: SQL Count Help Reply with quote

Dear Friends :
I need help, with a SQL COUNT, like this :
COUNT FIELD1, FIELD2, FIELD3, GROUPED BY FIELD1 and FIELD2
I need to do , many queries , always grouped by field 1 and 2 and only FIELD3, will change !
Yours truly,
Newton
Back to top
View user's profile Send private message Send e-mail Yahoo Messenger
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Mon Aug 22, 2011 10:40 am    Post subject: Reply with quote

Newton:

I would like to help you, but, I am sorry, I do not understand exactly what you want.

Could you please give a better explanation of what you want to count and what the output would look like.

Maybe, you could provide a few ( maybe 10 ) rows of data, and, from the sample data, what the output should look like. That way, someone here could suggest the SQL Query that will make it possible.

Sliderule
Back to top
View user's profile Send private message
nicksbr
General User
General User


Joined: 20 Jun 2005
Posts: 23
Location: Brazil

PostPosted: Tue Aug 23, 2011 8:23 am    Post subject: Sample Reply with quote

Dear Sliderule :
Sorry for the confusion and, I hope that you can understand :

Table
FIELD1 FIELD2 FIELD3
AAAAA JJJ III
BBBBB TTT UU
AAAAA JJJ III
BBBBB TTT UU
CCCCC JJJ UU
AAAAA JJJ III
AAAAA TTT BB
BBBBB TTT UU

I need a count like this, with number of ocurrences :
AAAAA JJJ III 2
AAAAA TTT BB 1
BBBBB TTT UU 2
CCCCC JJJ UU 1

I tried, using GUI (Group and COUNT) , but I got an error, when you want two or more fields to count !
Only,through SQL, is possible ?
Back to top
View user's profile Send private message Send e-mail Yahoo Messenger
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Tue Aug 23, 2011 9:08 am    Post subject: Reply with quote

OK, but, I have to comment, the desired data returned you posted above is INCORRECT. Smile

I mean, AAAAA JJJ III should be 3, not 2, and, BBBBB TTT UU should be 3, not 2. Smile

If you are using the GUI ( Graphical User Interface ) to create your Query, see the link and comments below:



The equivalent as SQL is ( of course, change the table ( "MYTABLE" ) and field names to what is in your database ) :

Code:
SELECT
   "MYTABLE"."FIELD1",
   "MYTABLE"."FIELD2",
   "MYTABLE"."FIELD3",
   COUNT( * ) AS "Count"

FROM "MYTABLE" AS "MYTABLE"

GROUP BY "MYTABLE"."FIELD1",
         "MYTABLE"."FIELD2",
         "MYTABLE"."FIELD3"

ORDER BY "MYTABLE"."FIELD1" ASC,
         "MYTABLE"."FIELD2" ASC,
         "MYTABLE"."FIELD3" ASC


Explanation: You probably had an error, because, this is an AGGREGATE QUERY. Therefore, for EACH column in the Select clause ( marked as Visible ) . . . it MUST use a FUNCTION. In your case, you wanted three GROUP functions, and, 1 COUNT function to match the 4 Visible columns. Furthermore, if a column is NOT MARKED as Visibile, this is fine, and, could be used for example, on the Criterion line, to include conditions to return only some of the records from your table. Smile

I hope this helps, please be sure to let me / us know. Smile

Sliderule

Thanks to add [Solved] in your first post Title ( edit button ) if your issue has been fixed / resolved.
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