October 21, 2004 at 8:15 am
What is the difference between group by and order by and what is the difference between having and where with example?
October 21, 2004 at 10:40 am
GROUP BY
divides the data into groups and produces a list of unique values (like DISTINCT) and allows counting, summation etc
so
select col1 from test group by col1
will produce the same as
select distinct col1 from test
and will have the same plan
with GROUP BY you can count the number of rows in the group, eg
select col1,count(*) from test group by col1
will produce each unique value of col1 with the count of the number of occurances of each value
WHERE
reduces the data set prior to grouping (GROUP BY) and/or sorting (ORDER BY)
HAVING
reduces the grouped data set after grouping
ORDER BY
sorts the final result (after any WHERE or HAVING) into the order specified
as for examples using the following data
create table test (col1 int)
insert into test (col1) values (1)
insert into test (col1) values (1)
insert into test (col1) values (1)
insert into test (col1) values (2)
insert into test (col1) values (2)
insert into test (col1) values (3)
insert into test (col1) values (3)
insert into test (col1) values (3)
insert into test (col1) values (3)
insert into test (col1) values (4)
insert into test (col1) values (4)
insert into test (col1) values (5)
select col1,count(*) as [count] from test group by col1
col1 count
1 3
2 2
3 4
4 2
5 1
select col1,count(*) as [count] from test where col1 in (1,3,5) group by col1
col1 count
1 3
3 4
5 1
select col1,count(*) as [count] from test group by col1 having count(*) > 1
col1 count
1 3
2 2
3 4
4 2
select col1,count(*) as [count] from test group by col1 having count(*) > 1 order by count(*) desc
col1 count
3 4
1 3
2 2
4 2
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply