Toad World® Forums

Grouping problems????


#1

I have a quert set up to a small database, and when i run the query to gather the information i need with WHERE conditions set all works fine and i have 38 results, which is what it should be.
Now in these results is an age column that contains ages between 6 to 15 with . 5 increments. (eg. 6, 6.5, 7, 7.5 and so on)
Now in the 38 results i get many of these results have the same age and i want to GROUP the results into ages, starting from youngest to oldest.

Now I selected Group in the Age Column and it said Group 1.
Then as i understand i also need to set up a Having Condition to be linked to the Group.
This is where i run into problems as nothing i try works.
The best option i get is 15 results from the 38, it just list the result that contains the 1st number in age. For example 8.5 i know there is 5 results with 8.5 however it only displays 1.
What am i doing wrong, is there someone who can help me.

Thank you


#2

When I read your post, I get the impression that you want to ORDER the results by age, not GROUP by age.
A GROUP statement will only return one row per group. So you would expect that a SELECT with a GROUP will return less than the total number of rows. Typicall you use a GROUP with a statistical function in the SELECT (something like SUM, MIN, MAX, AVG).

Example: I have table of people with NAME and AGE. My table has seven rows. Look at the examples below to see the difference between ORDER and GROUP.

SQL> select age, name from people ;
AGE NAME


   10 SMITH
   20 BROWN
   30 BRYAN
   40 BERGER
   20 HOLMES
   10 JONES
   20 WILLIAMS

SQL> select age, name from people ORDER BY age ;
AGE NAME


   10 SMITH
   10 JONES
   20 BROWN
   20 HOLMES
   20 WILLIAMS
   30 BRYAN
   40 BERGER

SQL> select age from people GROUP BY age ;
AGE

   10
   20
   30
   40

SQL> select age, count () from people GROUP BY age ;
AGE COUNT(
)


   10         2
   20         3
   30         1
   40         1

#3

Students.FirstName,

Students.LastName,

Students.Age,

Students.Gender,

Students.EngYears,

accepts.acceptW1AS,

accepts.acceptW2AS,

Students.PrivateSchool,

Students.PublicSchool,

Students.IndLessons,

Students.DiffCountry,

Students.Location,

Students.English,

Students.regDate

INNER JOIN

hproje15_American.accepts accepts

WHERE (accepts.acceptW1AS = ‘Yes’)

AND (Students.English = ‘AmericanSummer - 2010’)

select Age from Students GROUP BY Age ;

Thank you for the explaination on the differance between the two. It is clear now and you are correct I want to order the results by age.

I am assuming i should add this manually into the query as i do not see a funtion to select ORDER BY

This is my Query so far which gives the results of all 38 enteries
//Start of Query code\\
SELECT Students.ID,FROM hproje15_American.Students StudentsON (Students.ID = accepts.ID)AND (Students.Location = ‘Szkola Podstawowa nr.5’)//End of Query code\\
should I now add at the bottom, or where should i add it, I am not good in this type of coding.
(sorry for my bad English)


#4

Ok i worked it out now.

Thanks for all your help it is working perfectly now.

Damon


#5

I am guessing that you found the way to ORDER BY a certain column using the Query Builder in Toad for Data Analysts?