| View previous topic :: View next topic |
| Author |
Message |
nicksbr
General User
Joined: 20 Jun 2005
Posts: 23
Location: Brazil
|
 Posted: Mon Aug 22, 2011 6:57 am Post subject: SQL Count Help |
 |
|
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 |
|
 |
Sliderule
Super User
Joined: 29 May 2004
Posts: 2474
Location: 3rd Rock From The Sun
|
| Posted: Mon Aug 22, 2011 10:40 am Post subject: |
|
|
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 |
|
|
nicksbr
General User
Joined: 20 Jun 2005
Posts: 23
Location: Brazil
|
| Posted: Tue Aug 23, 2011 8:23 am Post subject: Sample |
|
|
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 |
|
|
Sliderule
Super User
Joined: 29 May 2004
Posts: 2474
Location: 3rd Rock From The Sun
|
| Posted: Tue Aug 23, 2011 9:08 am Post subject: |
|
|
OK, but, I have to comment, the desired data returned you posted above is INCORRECT. 
I mean, AAAAA JJJ III should be 3, not 2, and, BBBBB TTT UU should be 3, not 2.
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.
I hope this helps, please be sure to let me / us know.
Sliderule
Thanks to add [Solved] in your first post Title ( edit button ) if your issue has been fixed / resolved. |
|
| Back to top |
|
|
|
|
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
|
