DISTINCT-GROUP BY

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
  

Poston Wed, 05 Mar 2008 4:15 pm

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

DISTINCT GROUP BY-what's the difference, because both bring the same result??
If possible, mention another query ...


Codigo1
Code: Select all
SELECT DISTINCT deptno
FROM emp;
Code 2
Code: Select all
SELECT  deptno
FROM emp group by deptno;
thanks guys ...
Dulima-SP
Location: SP

Dulima

DBA Oracle Jr

Poston Wed, 05 Mar 2008 4:46 pm

following this example, they have no difference ... more if u try this ...

Code: Select all
SELECT DISTINCT deptno, ENAME
FROM emp
/
or just
Code: Select all
SELECT  deptno, ENAME
FROM emp group by deptno
/
will see qui is not the same result more .... and também 2 code not work Thurs. ... the distinct he cheat the distinct fields, but when has a different value he já cheat another line ... the group by is something a pouco more complicated, Thurs I will not be able to explain = D more I'll find a definition here and já post .... = D
gokden
Location: Ribeirão Preto - SP

Lucas de Souza

OCA Developer
Analista de sistemas

Poston Wed, 05 Mar 2008 4:56 pm

understand friend ... more if you put the Script Just goes ....

Code: Select all
SELECT  deptno, ENAME
FROM emp group by deptno, ename;
To where I know every GROUP BY must have the fields in the Select ...


I appreciate the collaboration gokden .... more to Doubt perciste!!!
Dulima-SP
Location: SP

Dulima

DBA Oracle Jr

Poston Wed, 05 Mar 2008 5:01 pm

Yes more group by function, is to group the rows from the select ... is qui faiz poco time Thurs I intend the group by = x so Thu I don't know explain muito ... until a while ago I rode the select and was putting the fields in group by work = D I will post what I found here, then you can post your questions ...

Creating data Groups: GROUP BY clause
SELECT column, group_function (column) FROM table [WHERE condition] [GROUP BY group_by_expression] [ORDER BY expr];


You can use the GROUP BY clause to divide the rows of a table into groups. You can then use the Group functions to return information summarized for each group.

Syntax: group_by_expression specifies the columns whose values determine the basis for the grouping of lines.

Guidelines * If you include a group function in a SELECT clause, you cannot select individual results unless the individual column appear 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 rows before making the Division of groups.
* You must include the columns in the GROUP BY clause.
* You cannot use the alias of a column in the GROUP BY clause.
* By default, the rows are sorted in ascending order of the columns listed in the GROUP BY clause. You can override this order using the ORDER BY clause
.

Example:
SELECT deptno, AVG (sal) FROM emp GROUP BY deptno;
gokden
Location: Ribeirão Preto - SP

Lucas de Souza

OCA Developer
Analista de sistemas

Poston Wed, 05 Mar 2008 5:16 pm

Long story short .... the functions Count Sum Max AVG ....

So can be with that group by max_write_lock_count system ... and the distinct creates a \"group\" more without these functions ...

RIGHT????
Dulima-SP
Location: SP

Dulima

DBA Oracle Jr

Poston Wed, 05 Mar 2008 5:54 pm

by Thurs I intend ... the distinct, transforms the identical lines in a row and the group by grouping the fields when you use the SUM, AVG, MAX, MIN (Group functions) for you group by something ... ex: I want to know the sum total of SALT field of table EMP
Code: Select all
select sum(sal) from emp;
now I want to know the sum of each salt DEPTONO of table EMP. understand??

Code: Select all
select sum(sal), deptno from emp group by deptno;
gokden
Location: Ribeirão Preto - SP

Lucas de Souza

OCA Developer
Analista de sistemas

Poston Wed, 05 Mar 2008 6:01 pm

Understand Man. ...

Thanks ....

I'm going to play with these commands ... anything I post .... thanks!!
Dulima-SP
Location: SP

Dulima

DBA Oracle Jr



Return to SQL

Who is online

Users browsing this forum: Google [Bot] and 4 guests