Home » SQL & PL/SQL » SQL & PL/SQL » Group statement
Group statement [message #179328] Mon, 26 June 2006 22:29 Go to next message
rmalhi
Messages: 42
Registered: May 2006
Location: Australia
Member
Message details deleted at posters request

[Updated on: Thu, 01 May 2008 02:17] by Moderator

Report message to a moderator

Re: Group statement [message #179340 is a reply to message #179328] Tue, 27 June 2006 00:58 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
I used this script (next time, please provide your own create table and insert statements in code tags):
SET SCAN OFF

CREATE TABLE mhe_foo(login VARCHAR2(10),   showname VARCHAR2(30))
/

INSERT INTO mhe_foo VALUES('bilfinger', 'Bilfinger-Berger - Customer'    )
/
INSERT INTO mhe_foo VALUES('telalert' , 'TelAlert - EMC'                 )
/
INSERT INTO mhe_foo VALUES('nthline'  , 'nthline'                        )
/
INSERT INTO mhe_foo VALUES('mirvac'   , 'Mirvac - Customer'              )
/
INSERT INTO mhe_foo VALUES('hutch'    , 'hutch'                          )
/
INSERT INTO mhe_foo VALUES('sasalemo' , 'Solomon Asalemo - D&P'          )
/
INSERT INTO mhe_foo VALUES('drichards', 'Deborah Richards - CS&S'        )
/
INSERT INTO mhe_foo VALUES('novtest'  , 'NovogenTest'                    )
/
INSERT INTO mhe_foo VALUES('smanku'   , 'Sandeep Manku - EMC'            )
/
INSERT INTO mhe_foo VALUES('skhan'    , 'Sayem Khan - EMC'               )
/
INSERT INTO mhe_foo VALUES('bwilson'  , 'Ben Wilson - EMC'               )
/
INSERT INTO mhe_foo VALUES('trowan'   , 'Thomas Rowan - QLD Health'      )
/
INSERT INTO mhe_foo VALUES('cellis'   , 'Christopher Ellis - QLD Health' )
/

PROMPT Use instr/substr to break the string:
PROMPT Note the use of DECODE in case no '-' exists:

SELECT login
     , TRIM( SUBSTR(showname,1,DECODE( INSTR(showname,'-')
                                     , 0, LENGTH(showname)
                                     , INSTR(showname,'-')-1
                                     )
                   )
           ) showname
     , TRIM(SUBSTR(showname,INSTR(showname,'-')+1,length(showname))) customer
FROM   mhe_foo
/

PROMPT The result:
WITH theselect AS ( SELECT login
                         , TRIM( SUBSTR(showname,1, DECODE( INSTR(showname,'-')
                                                          , 0, LENGTH(showname)
                                                          , INSTR(showname,'-')-1
                                                          )
                                       ) 
                               ) showname
                         , TRIM(SUBSTR(showname,INSTR(showname,'-')+1,length(showname))) customer
                    FROM   mhe_foo
                  )
SELECT showname
FROM   theselect
WHERE  customer = 'EMC'
/

DROP TABLE mhe_foo
/

On My box it ran like this:
SQL> @C:\useful\orafaq

Table created.


1 row created.


1 row created.


1 row created.


1 row created.


1 row created.


1 row created.


1 row created.


1 row created.


1 row created.


1 row created.


1 row created.


1 row created.


1 row created.

Use instr/substr to break the string:
Note the use of DECODE in case no '-' exists:

LOGIN      SHOWNAME                       CUSTOMER
---------- ------------------------------ ------------------------------
bilfinger  Bilfinger                      Berger - Customer
telalert   TelAlert                       EMC
nthline    nthline                        nthline
mirvac     Mirvac                         Customer
hutch      hutch                          hutch
sasalemo   Solomon Asalemo                D&P
drichards  Deborah Richards               CS&S
novtest    NovogenTest                    NovogenTest
smanku     Sandeep Manku                  EMC
skhan      Sayem Khan                     EMC
bwilson    Ben Wilson                     EMC

LOGIN      SHOWNAME                       CUSTOMER
---------- ------------------------------ ------------------------------
trowan     Thomas Rowan                   QLD Health
cellis     Christopher Ellis              QLD Health

13 rows selected.

The result:

SHOWNAME
------------------------------
TelAlert
Sandeep Manku
Sayem Khan
Ben Wilson


Table dropped.

SQL>


MHE
Re: Group statement [message #179352 is a reply to message #179340] Tue, 27 June 2006 01:55 Go to previous messageGo to next message
rmalhi
Messages: 42
Registered: May 2006
Location: Australia
Member
Thanks Mate,

But i have ran your select statement like also tried your script.

select ACC_SHOWNAME , TRIM(SUBSTR(acc_showname,INSTR(acc_showname,'-')+1,length(acc_showname))) showname from REP_accounts;

I want the results like

QLD Health
all the names like
trowan Thomas Rowan QLD Health
cellis Christopher Ellis QLD Health

EMC
smanku Sandeep Manku EMC
skhan Sayem Khan EMC
bwilson Ben Wilson EMC

AND SO ON.

I NEED TO PUT THE QUERY IN REPORT. I AM SORRY IF I DID NOT EXPLAINED YOU BEFORE. AS I HAVE MENTIONED BEFORE I AM NOT THAT GOOD IN SQL.

Thansk in advance.

Cheers
Rupi






Re: Group statement [message #179355 is a reply to message #179352] Tue, 27 June 2006 02:07 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
I see. What version are you on? Oh, one tip: surround code by [code] and [/code] so it preserves formatting. For more tips, read the first subject in this forum.

MHE
Re: Group statement [message #179356 is a reply to message #179328] Tue, 27 June 2006 02:13 Go to previous messageGo to next message
rmalhi
Messages: 42
Registered: May 2006
Location: Australia
Member
Thanks Maaher for your update. i will try to group the alphabets after the - .
Like
Sandeep Manku - EMC

all the persons after - in this case EMC.

your script will help. Thanks again.

Regards
Rupi
Re: Group statement [message #179357 is a reply to message #179356] Tue, 27 June 2006 02:18 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
A slightly modified version. The names without a second part become 'None':
SET SCAN OFF

CREATE TABLE mhe_foo(login VARCHAR2(10),   showname VARCHAR2(30))
/

INSERT INTO mhe_foo VALUES('bilfinger', 'Bilfinger-Berger - Customer'    )
/
INSERT INTO mhe_foo VALUES('telalert' , 'TelAlert - EMC'                 )
/
INSERT INTO mhe_foo VALUES('nthline'  , 'nthline'                        )
/
INSERT INTO mhe_foo VALUES('mirvac'   , 'Mirvac - Customer'              )
/
INSERT INTO mhe_foo VALUES('hutch'    , 'hutch'                          )
/
INSERT INTO mhe_foo VALUES('sasalemo' , 'Solomon Asalemo - D&P'          )
/
INSERT INTO mhe_foo VALUES('drichards', 'Deborah Richards - CS&S'        )
/
INSERT INTO mhe_foo VALUES('novtest'  , 'NovogenTest'                    )
/
INSERT INTO mhe_foo VALUES('smanku'   , 'Sandeep Manku - EMC'            )
/
INSERT INTO mhe_foo VALUES('skhan'    , 'Sayem Khan - EMC'               )
/
INSERT INTO mhe_foo VALUES('bwilson'  , 'Ben Wilson - EMC'               )
/
INSERT INTO mhe_foo VALUES('trowan'   , 'Thomas Rowan - QLD Health'      )
/
INSERT INTO mhe_foo VALUES('cellis'   , 'Christopher Ellis - QLD Health' )
/

SELECT TRIM( SUBSTR(showname,1,DECODE( INSTR(showname,'-')
                                     , 0, LENGTH(showname)
                                     , INSTR(showname,'-')-1
                                     )
                   )
           ) showname
     , DECODE( INSTR(showname,'-')
             , 0, 'None'
             , TRIM(SUBSTR(showname,INSTR(showname,'-')+1,LENGTH(showname)))
             ) customer
FROM   mhe_foo
ORDER BY customer
/

PROMPT The result:
WITH theselect AS ( SELECT login
                         , TRIM( SUBSTR(showname,1, DECODE( INSTR(showname,'-')
                                                          , 0, LENGTH(showname)
                                                          , INSTR(showname,'-')-1
                                                          )
                                       ) 
                               ) showname
                         , DECODE(INSTR(showname,'-')
                                 , 0, 'None'
                                 , TRIM(SUBSTR(showname,INSTR(showname,'-')+1,LENGTH(showname)))
                                 )
                             customer
                    FROM   mhe_foo
                  )
SELECT DECODE(customer, next_customer, NULL, customer) customer
     , login
     , showname
FROM   ( SELECT login
              , showname
              , customer
              , LAG(customer) OVER ( ORDER BY customer, login ) next_customer
         FROM   theselect
       )

/

DROP TABLE mhe_foo
/

When ran, the script showed this:
SQL> @C:\useful\orafaq

Table created.


1 row created.


1 row created.


1 row created.


1 row created.


1 row created.


1 row created.


1 row created.


1 row created.


1 row created.


1 row created.


1 row created.


1 row created.


1 row created.


SHOWNAME                       CUSTOMER
------------------------------ ------------------------------
Bilfinger                      Berger - Customer
Deborah Richards               CS&S
Mirvac                         Customer
Solomon Asalemo                D&P
TelAlert                       EMC
Sayem Khan                     EMC
Ben Wilson                     EMC
Sandeep Manku                  EMC
nthline                        None
NovogenTest                    None
hutch                          None

SHOWNAME                       CUSTOMER
------------------------------ ------------------------------
Thomas Rowan                   QLD Health
Christopher Ellis              QLD Health

13 rows selected.

The result:

CUSTOMER                       LOGIN      SHOWNAME
------------------------------ ---------- ------------------------------
Berger - Customer              bilfinger  Bilfinger
CS&S                           drichards  Deborah Richards
Customer                       mirvac     Mirvac
D&P                            sasalemo   Solomon Asalemo
EMC                            bwilson    Ben Wilson
                               skhan      Sayem Khan
                               smanku     Sandeep Manku
                               telalert   TelAlert
None                           hutch      hutch
                               novtest    NovogenTest
                               nthline    nthline

CUSTOMER                       LOGIN      SHOWNAME
------------------------------ ---------- ------------------------------
QLD Health                     cellis     Christopher Ellis
                               trowan     Thomas Rowan

13 rows selected.


Table dropped.


Does this help?

MHE
Re: Group statement [message #191123 is a reply to message #179357] Mon, 04 September 2006 20:59 Go to previous messageGo to next message
rmalhi
Messages: 42
Registered: May 2006
Location: Australia
Member
Hi,

Thanks for your all the replies before.

I want to count the same result in group by query.
like
SQL> select SES_CLIENT_IPADDRESS from rep_sessions
2 group by SES_CLIENT_IPADDRESS;

SES_CLIENT_IPADDRESS
--------------------------------------------------------------------------------
bill-desktop.cmchelpdesk.local
cd075640.fm.optus.net.au
cd075807.fm.optus.net.au
citrix01.optus.com.au
emc-andrewl.cmchelpdesk.local
emc-cmani.cmchelpdesk.local
emc-grantc.cmchelpdesk.local
emc-hassanc.cmchelpdesk.local
emc-hughm1.cmchelpdesk.local
emc-jtan.cmchelpdesk.local
emc-mso-stat1.cmchelpdesk.local

SES_CLIENT_IPADDRESS
--------------------------------------------------------------------------------
emc-nc6000-iag1.cmchelpdesk.local
emc-nc6k-damian.cmchelpdesk.local
emc-ordesk.cmchelpdesk.local
emc-ptakac.cmchelpdesk.local
emc-rodonnell.cmchelpdesk.local
emc-tier1-stat1.cmchelpdesk.local
eoc-001
eoc-dw2
eoc-sl
eoc-sw
gavin

SES_CLIENT_IPADDRESS
--------------------------------------------------------------------------------
melnsc21.alphawest.com.au
msg-n610csam.cmchelpdesk.local
msg-nc6000anil.cmchelpdesk.local
qh10161097
qh10161939
qld-elccz5olj81
sd-web
snoc-desk02.cmchelpdesk.local
snoc-desk04.cmchelpdesk.local
snoc-desk05.cmchelpdesk.local
snoc-desk06.cmchelpdesk.local

33 rows selected.
*****************************

I want to count all the session with there first 3 alphanets.
like
out of 33 rows selested

emc-nc6000-iag1.cmchelpdesk.local
emc-nc6k-damian.cmchelpdesk.local
emc-ordesk.cmchelpdesk.local
emc-ptakac.cmchelpdesk.local
emc-rodonnell.cmchelpdesk.local
emc-tier1-stat1.cmchelpdesk.local
-- - - - - - -- - - -
EMC= 13

- - - - - - -
snoc-desk02.cmchelpdesk.local
snoc-desk04.cmchelpdesk.local
snoc-desk05.cmchelpdesk.local
snoc-desk06.cmchelpdesk.local

snoc = 10 (just and idea)
qh10161097
qh10161939
qld-elccz5olj81

qh=10

Please help me in this.

Regards
Rupi
Re: Group statement [message #193153 is a reply to message #191123] Fri, 15 September 2006 01:50 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
You are unclear in your requirements: how do you get to figures like 10 for 'snoc' (which is 4 characters and that is not according to your requirements) and 10 for 'qh' (which is 2 characters and that is not according to your requirements too)?

May I ask to post:
- table creation script with code tags and sample data. No select, but insert statements.
- expected output with code tags.

MHE
Re: Group statement [message #193156 is a reply to message #193153] Fri, 15 September 2006 01:53 Go to previous messageGo to next message
rmalhi
Messages: 42
Registered: May 2006
Location: Australia
Member
Thanks for your reply.
here is the details

I need a help related count by first letters of showname
I am running the following query and please see the result. I am going thro ORAFAQ and got the point that I have to use some sort of count and decode function. But not able to make it working.

select RS.ses_created, RA.acc_showname
from REP_SESSIONS RS,REP_ACCOUNTS RA
where RA.acc_oid=RS.ses_acc_oid
order by RA.acc_showname;

SES_CREAT ACC_SHOWNAME
--------- --------------------------------------------------
15-SEP-06 AW - Mie Cwell
15-SEP-06 D&P - Ae-Mie Wi
15-SEP-06 D&P - Diel Ndf
14-SEP-06 D&P - Soln Amo
15-SEP-06 D&P - Ththu Haas
15-SEP-06 EMC - Aj Jana
15-SEP-06 EMC - An Pl
15-SEP-06 EMC – Chan Calar
14-SEP-06 EMC - Christo Ma
15-SEP-06 EMC - Daan Mes
15-SEP-06 EMC - Ec Ser
15-SEP-06 EMC - Ez Tni
15-SEP-06 EMC - Gra Cesas
14-SEP-06 EMC - Ian Milne
15-SEP-06 EMC - Jeff Tn
14-SEP-06 EMC - Mic Hi
15-SEP-06 EMC - Pa Xcs
14-SEP-06 EMC - Per Seed
14-SEP-06 EMC - Rn Mam
14-SEP-06 EMC - Shacy Anson
14-SEP-06 QLD Health - Gin Xper
15-SEP-06 QLD Health - Bs Os
15-SEP-06 QLD Health - Logins - Rham Jenk
14-SEP-06 QLD Health - Loa Ka
15-SEP-06 QLD Health - Mar Edd
15-SEP-06 QLD Health - Oexc
14-SEP-06 QLD Health - Swerer
14-SEP-06 QLD Health - Sere
15-SEP-06 System administrator

29 rows selected.

I want to display the results like

ACC_SHOWNAME

QLD Health = 8
EMC =14
D&P=4
And so on ------
Re: Group statement [message #193163 is a reply to message #193153] Fri, 15 September 2006 02:09 Go to previous messageGo to next message
rmalhi
Messages: 42
Registered: May 2006
Location: Australia
Member
Maaher,

What i want is total of the names starting with "EMC",QLD Health, D&P,AW etc.
i want to count the alphabets before the name.
as "EMC",QLD Health, D&P,AW are all different group. I want to see how many people belogs to same group loged on that day.

Thank in advance.

Warm Regards
Rupi
Re: Group statement [message #193203 is a reply to message #193153] Fri, 15 September 2006 04:53 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Maaher wrote on Fri, 15 September 2006 08:50

May I ask to post:
- table creation script with code tags and sample data. No select, but insert statements.
- expected output with code tags.

MHE


MHE
Re: Group statement [message #193487 is a reply to message #193203] Sun, 17 September 2006 19:25 Go to previous messageGo to next message
rmalhi
Messages: 42
Registered: May 2006
Location: Australia
Member
Thanks Maaher for your time and concern. I really appreciate your all help from bottom of my heart. As you always says first search on ORAFAQ. I have done this and also looked on the net. This is the reason i am bother you again and again. I am sorry for this. I have tried to explain the problem and what you want.
Please find the attached document please.

Thanks again

Regards
Rupinder
Re: Group statement [message #193502 is a reply to message #193203] Sun, 17 September 2006 21:19 Go to previous message
rmalhi
Messages: 42
Registered: May 2006
Location: Australia
Member
Maaher,

I have got the script working. I have modified the script you have send before and it works. Thanks for your help.
may be save the energy for next problem.

Warm Regards
Rupi
Previous Topic: All records
Next Topic: Is In operator will work?
Goto Forum:
  


Current Time: Mon Dec 05 03:12:53 CST 2016

Total time taken to generate the page: 0.23494 seconds