Distinct - Group by

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
Post Reply
Dulima-SP
Rank: DBA Júnior
Rank: DBA Júnior
Posts: 193
Joined: Thu, 24 Jan 2008 1:56 pm
Location: SP
Dulima

DBA Oracle Jr

Gentlemen ...
I came across hj with the following situations.

Distinct - Group by

What are their difference, for the two Bring me the same result ???
If possible, mention another query ...


Code1

Select all

 
SELECT DISTINCT deptno 
 FROM emp;
Code 2
]

Select all

SELECT  deptno 
 FROM emp group by deptno;

Valeu galera ..
gokden
Rank: DBA Pleno
Rank: DBA Pleno
Posts: 264
Joined: Sun, 19 Aug 2007 8:18 pm
Location: Ribeirão Preto - SP
Lucas de Souza

OCA Developer
Analista de sistemas

Following this example yours, they have no difference even ...
more if you try like this ...

Select all

SELECT DISTINCT deptno, ENAME 
FROM emp 
/ 
or so

Select all

 
SELECT  deptno, ENAME 
FROM emp group by deptno 
/ 
will see chi is not the same result more ....
and também chicken 2 will not work ....

99] The distinct he bets the distinct fields, but when he has a different value he já trais another line ...

The group by is a thing a more complicated pouco, that I will not I can explain = D

more I'll look for a definition here and já post .... = D
Dulima-SP
Rank: DBA Júnior
Rank: DBA Júnior
Posts: 193
Joined: Thu, 24 Jan 2008 1:56 pm
Location: SP
Dulima

DBA Oracle Jr

I understand friend ...

more if you put the script so goes ....

Select all

 
SELECT  deptno, ENAME 
FROM emp group by deptno, ename; 
I know, all group by needs to have the fields of select ...


Thank you Gokden The collaboration ....
More In doubt Persse !!!
gokden
Rank: DBA Pleno
Rank: DBA Pleno
Posts: 264
Joined: Sun, 19 Aug 2007 8:18 pm
Location: Ribeirão Preto - SP
Lucas de Souza

OCA Developer
Analista de sistemas

Yes more the role of group by, is to group the lines of the select ...

is qui faiz poco time qui I intendi the group by = x
so I do not know explain muito well ...

up to a while ago I rode the SELECT and I was putting the fields in the group by until it works = D
I'll post what I found here, You can post your doubts ...
Creating data groups: clause Group by


You can use the Group BY clause to split the rows from a table in groups. You can then use the group functions to return summarized information for each group. [/ I]

Syntax: group_by_expression Specifies the columns whose values ??determine the Base for the grouping of lines.

Guidelines

* If you include a group function in a SELECT clause, You can not select individual results unless the individual column appears in the Group BY clause. You will receive an error message if you do not include the column in the list.
* Using the WHERE clause, you can delete lines before making the division of the groups.
* You must include the columns in the Group BY clause.
* You can not use the alias of a column in the Group BY clause.
* By default, the rows are classified in ascending order of the columns included in the Group BY clause list. You can overlap this order using the Order By
clause.

Example:

Select all

SELECT	deptno, AVG(sal) 
  FROM		emp 
  GROUP BY	deptno;[/quote]
Dulima-SP
Rank: DBA Júnior
Rank: DBA Júnior
Posts: 193
Joined: Thu, 24 Jan 2008 1:56 pm
Location: SP
Dulima

DBA Oracle Jr

Summarizing ....

The functions
count
Sum
Max
AVG
....

may be used with such group by .....

and the distinct creates a "group" more without these functions ...

right ????
gokden
Rank: DBA Pleno
Rank: DBA Pleno
Posts: 264
Joined: Sun, 19 Aug 2007 8:18 pm
Location: Ribeirão Preto - SP
Lucas de Souza

OCA Developer
Analista de sistemas

by the chi I intendic ....
the distinct, tansform the identical lines in a line only

and the group by groups the fields when you use the sum, avg, max , Min (group functions) for you to group for something ...

ex: I want to know the total sum of the salt field of the EMP table

Select all

select sum(sal) from emp;
Now I want to know the sum of the salt of each depth of the EMP table. Intenda ??

Select all

 select sum(sal), deptno from emp group by deptno;
Dulima-SP
Rank: DBA Júnior
Rank: DBA Júnior
Posts: 193
Joined: Thu, 24 Jan 2008 1:56 pm
Location: SP
Dulima

DBA Oracle Jr

I understood face ....

Thanks ....

I will play with these commands ...

I post ....

Valeu !!
Post Reply
  • Information
  • Who is online

    Users browsing this forum: No registered users and 11 guests