Groupoing?

[informer30]

Hi All,

I am to programming...

In oracle when writing a statement does the grouping affect the results..?

Cheers

 

[Turkbear]

Hi,
Welcome to this odd world.

The results themselves are mostly determkined by the SQL in the query that specifies the table(s),fields from that(those) tables and the restrictions in any where clause(s).

The Grouping ( or lack of it) can change how the returned data is ordered:

Select state,city,name from DemoTable -->:
Arizona,Phoenix,Jones
Arizona,Phoenix,Smith
Arizona,Tempe,Sanchez

Select state,city,name from DemoTable, order by name -->:
Arizona,Phoenix,Jones
Arizona,Tempe,Sanchez
Arizona,Phoenix,Smith

Select state,city,name from DemoTable, group by state,city -->:
Arizona
Phoenix
Jones
Smith
Tempe
Sanchez








To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[taupirho]

Yes


In order to understand recursion, you must first understand recursion.

 

[SantaMufasa]

Informer30,

GROUP BY compresses into a single row, one representative row per group. Here is an illustration from Oracle Education's s_emp table:

select dept_id,last_name from s_emp;

DEPT_ID LAST_NAME
------- ------------
     50 Velasquez
     41 Ngao
     31 Nagayama
     10 Quick-To-See
     50 Ropeburn
     41 Urguhart
     42 Menchu
     43 Biri
     44 Catchpole
     45 Havel
     31 Magee
     32 Giljum
     33 Sedeghi
     34 Nguyen
     35 Dumas
     41 Maduro
     41 Smith
     42 Nozaki
     42 Patel
     43 Newman
     43 Markarian
     44 Chang
     34 Patel
     45 Dancs
     45 Schwartz

25 rows selected.

select dept_id,count(*) from s_emp group by dept_id;
   DEPT_ID   COUNT(*)
---------- ----------
        42          3
        43          3
        34          2
        44          2
        31          2
        32          1
        35          1
        50          2
        41          4
        45          3
        33          1
        10          1
           ----------
sum                25

12 rows selected.

Notice how the original 25 rows compressed down to 12 grouped rows with the GROUP BY clause, since there are 12 distinct dept_id values. (BTW, if you want GROUP BY rows to return in a particular order, then add an ORDER BY clause to the SELECT statement, as well.)

Let us know if this helps to understand the GROUP BY clause.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[informer30]

That's brill Santa...I appreciate it...

Many Thanks