Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

Development Tools Database Foundations SQL Calculating, Aggregating and Other Functions Grouping, Joining and Cleaning Up

Posted by ahmed saleh
ahmed saleh
2,754 Points

group by doesn't work

hi guys see this image and you can see what i mean it is giving same values all the time enter image description here

its giving me same result in min and max and avg

thank you

ericb2
ericb2
11,429 Points

I'm a bit rusty (or more accurately, was never polished), but your result seems to be what I would expect. Your Database has 3 StudentIDs each with one Mark. Your query is asking for the MIN, MAX, and AVG Mark of EACH studentID, not ALL studentIDs. Since each ID has only one mark, MIN = MAX = AVG. If you wish the MIN, MAX, AVG collectively of all StudentIDs, leave off the 'group by StudentID' clause. Alternatively, insert additional (and different) Marks into your table for the existing StudentIDs.

ahmed saleh
ahmed saleh
2,754 Points

" leave off the 'group by StudentID' clause. Alternatively, insert additional (and different) Marks into your table for the existing StudentIDs." can you give me example please about how i can do that because when i deleted it's going to give 1 row about all records.. thank you for your replay

ericb2
ericb2
11,429 Points

Repeating your search without 'group by StudentID':

SELECT StudentID, MIN(Mark), MAX(Mark), AVG(Mark)
FROM Marks;

gives the result:

StudentID, MIN(Mark), MAX(Mark), AVG(Mark)
1          10          30          20.0000

which are the stats on all three studentIDs combined (despite #1 being the only ID displayed).

However, if instead you wanted stats on individual studentIDs, you need more than one value for each ID and must first add data, for example:

INSERT INTO Marks VALUES (1, 11);
INSERT INTO Marks VALUES (2, 21);
INSERT INTO Marks VALUES (3, 31);

Now each StudentID has 2 (different) values, so when you re-run your original search, 

SELECT StudentID, MIN(Mark), MAX(Mark), AVG(Mark)
FROM Marks
GROUP BY StudentID;

you get:

StudentID, MIN(Mark), MAX(Mark), AVG(Mark)
1          10          11          10.5000
2          20          21          20.5000
3          30          31          30.5000