Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Complex select query help (Group by)

user12050217Oct 13 2009 — edited Dec 21 2009
Hi,
I am in need your help to create a single select query for below table data and output which requies. I am using oracle 10g on RHEL 5 version.

create table bustour(bussno number(4),start_date date,passengers number(2));
alter session set nls_date_format='dd-mm-yyyy';
insert into bustour values (4123,'04-10-2009',20);
insert into bustour values (4123,'05-10-2009',25);
insert into bustour values (4123,'05-10-2009',18);
insert into bustour values (4123,'06-10-2009',15);

insert into bustour values (6138,'03-10-2009',16);
insert into bustour values (6138,'03-10-2009',19);
insert into bustour values (6138,'04-10-2009',22);
insert into bustour values (6138,'04-10-2009',13);

insert into bustour values (4123,'07-10-2009',23);
insert into bustour values (4123,'08-10-2009',27);
insert into bustour values (4123,'11-10-2009',15);

insert into bustour values (6138,'05-10-2009',16);
insert into bustour values (6138,'05-10-2009',13);
insert into bustour values (6138,'05-10-2009',18);
insert into bustour values (6138,'05-10-2009',24);
insert into bustour values (6138,'07-10-2009',20);
insert into bustour values (6138,'08-10-2009',18);
SQL> select * from bustour;

    BUSSNO START_DATE PASSENGERS
---------- ---------- ----------
      4123 04-10-2009         20
      4123 05-10-2009         25
      4123 05-10-2009         18
      4123 06-10-2009         15
      6138 03-10-2009         16
      6138 03-10-2009         19
      6138 04-10-2009         22
      6138 04-10-2009         13
      4123 07-10-2009         23
      4123 08-10-2009         27
      4123 11-10-2009         15

    BUSSNO START_DATE PASSENGERS
---------- ---------- ----------
      6138 05-10-2009         16
      6138 05-10-2009         13
      6138 05-10-2009         18
      6138 05-10-2009         24
      6138 07-10-2009         20
      6138 08-10-2009         18

17 rows selected.

I want query output as below :

Bussno  start_date      end_Date   totalpassengers   maxpessenger  maxpassdate
4123    04-10-09        06-10-09          78              25         05-10-09
6138    03-10-09        04-10-09          70              22         04-10-09 
4123    07-10-09        11-10-09          65              27         08-10-09
6138    05-10-09        08-10-09         109              24         05-10-09
So, that we can know on which particular trip-schedule we got maximum passenger and date. (To calculate driver's % of bonus)

Please help me to write the query.

Best regards
Nisha
This post has been answered by Boneist on Oct 13 2009
Jump to Answer

Comments

Glen Conway

What version of SQLcl are you running? With the latest 18.2 version, this is what I see:

Capture.JPG

Gaz in Oz

What sqlcl version are you running?

What OS are you running sqlcl on?

What terminal are you running sqlcl in?

What settings are you using in your terminal session?

What font are you using?

What version of Oracle database are you connecting to?

...

user635344

1.sqlformat command works but I need to format column by myself

2. Oracle SQLDeveloper Command-Line (SQLcl) version: 18.1.1.0

3. Linux4.1.12-61.1.18.el7uek.x86_64 = Oracle Linux Server release 7.3

4, putty 0.67

5.Courier New 14p

8. Oracle RDBMS 12.2.0.1

9. JDK 8.181

10. I do not see any issue in SQLplus

user635344

pastedImage_0.png

Gaz in Oz

Thanks for posting the extra info.

In your screenshot, it appears you "set sqlformat" to something, ansiconsole?

What else do you set?

Post output from "show all+"

What changes have you made to the putty settings?

...as for your screenshot, looks like an issue with the view v$version in the first part:

SQL> version

Oracle SQLDeveloper Command-Line (SQLcl) version: 18.1.1.0

SQL> set sqlformat ansiconsole

SQL> select * from v$version;

BANNER                                                                           CON_ID

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production          0

PL/SQL Release 12.2.0.1.0 - Production                                                0

CORE    12.2.0.1.0      Production                                                            0

TNS for Linux: Version 12.2.0.1.0 - Production                                        0

NLSRTL Version 12.2.0.1.0 - Production                                                0

SQL> set sqlformat

SQL Format Cleared

SQL> /

BANNER                                                                               CON_ID

-------------------------------------------------------------------------------- ----------

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0

PL/SQL Release 12.2.0.1.0 - Production                                                    0

CORE    12.2.0.1.0      Production                                                                  0

TNS for Linux: Version 12.2.0.1.0 - Production                                            0

NLSRTL Version 12.2.0.1.0 - Production                                                    0

SQL>

...and in sqlplus.

$ echo -e "set lines 91\n select * from v\$version;" | sqlplus -s gaz/gaz

BANNER                                                                               CON_ID

-------------------------------------------------------------------------------- ----------

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0

PL/SQL Release 12.2.0.1.0 - Production                                                    0

CORE    12.2.0.1.0      Production                                                                0

TNS for Linux: Version 12.2.0.1.0 - Production                                            0

NLSRTL Version 12.2.0.1.0 - Production                                                    0

$

user635344

Hi

No extra parameters

I can see the same result in putty and VirtualBox console

output in sqlplus works fine

pastedImage_0.png

pastedImage_1.png

SQL> show all+
Show All:
========================================
_prelim OFF
appinfo is OFF and set to "SQLcl"
arraysize 15
autocommit OFF
autoprint OFF
autotrace OFF
BTitle OFF
SQL*Plus mode: OFF
Clear Screen mode: top
oracle.net.tns_admin=null
javax.net.ssl.trustStore=null
javax.net.ssl.trustStorePassword=null
javax.net.ssl.keyStore=null
javax.net.ssl.keyStorePassword=null
oracle.net.ssl_server_dn_match=null
oracle.net.ssl_version=null
colinvisible OFF
colsep " "
concat "." (hex 2e)
copycommit 0
define "&" (hex 26)
show desc is obsolete.
echo OFF
editfile "afiedt.buf"

embedded OFF
Encoding:UTF-8
errorlogging is OFF
escape OFF
exitcommit ON
FEEDBACK ON for 6 or more rows
heading ON
headsep "|" (hex 7c)
HISTORY
enabled
blacklist: show,history,connect,set
Do not show failed statements
linesize 150
long 80
longchunksize 80
newpage 1
null /*notset*/
numformat ""
numwidth 10
pagesize 38
PAUSE is OFF
release 1202000100
scan ON
secureliterals DEFAULT
serveroutput OFF
showmode OFF
space " "
spool OFF
sqlcode 0
sqlprompt "SQL> "
suffix "sql"
termout ON
time OFF
timing OFF
TNS Lookup locations
--------------------

  1. USER Home dir
    /home/oracle
  2. ORACLE_HOME
    /u00/app/product/12.2/network/admin

Location used:
-------------
/u00/app/product/12.2/network/admin

Available TNS Entries
---------------------
DEMO1
DEMO2
LISTENER_MOS2
LISTENER_MOS4
MOS2
MOS2PDB1
MOS3PDB1
MOS4
PAPX1
PDB2
PDB3
PDB4
PDB5
PGG1
PGG1
trimout ON
trimspool OFF
ttitle OFF and is the first few characters of the next SELECT statementUSER is "SYS"
verify ON
Oracle SQLDeveloper Command-Line (SQLcl) version: 18.2.0.0
No Wallet location defined

wrap : lines will be wrapped

Show All+ (Single Line):
========================================
_
12.1_longident mode: OFF
_restrict 0
Auto Reload NLS:null
blockterminator "." (hex 2e)
buffer SQL
CD : Default

SQLcl unsupported SQL*Plus command: show CLOSECURSOR COMMANDLINE CON_ID CON_NAME EDITION ENCODING ENCODINGS ERR JAVA LDAPCON LOAD NET NOVERWRITE SECUREDCOL SPPARAMETER SQLDEV SQLDEV2 SQLFORMAT SQLPATH SQLPLUSCOMPAT SYSTEMOUT TABLE TOPLEVEL UFI URL XMLFORMAT XQUERY
commandline true
CON_ID
------------------------------
3
CON_NAME
------------------------------
PDB1
EDITION
--------
ORA$BASE
Encoding:UTF-8
current Encoding:UTF-8
List of available encodings:
Big5
Big5-HKSCS
CESU-8
EUC-JP
EUC-KR
GB18030
GB2312
GBK
IBM-Thai
IBM00858
IBM01140
IBM01141
IBM01142
IBM01143
IBM01144
IBM01145
IBM01146
IBM01147
IBM01148
IBM01149
IBM037
IBM1026
IBM1047
IBM273
IBM277
IBM278
IBM280
IBM284
IBM285
IBM290
IBM297
IBM420
IBM424
IBM437
IBM500
IBM775
IBM850
IBM852
IBM855
IBM857
IBM860
IBM861
IBM862
IBM863
IBM864
IBM865
IBM866
IBM868
IBM869
IBM870
IBM871
IBM918
ISO-2022-CN
ISO-2022-JP
ISO-2022-JP-2
ISO-2022-KR
ISO-8859-1
ISO-8859-13
ISO-8859-15
ISO-8859-2
ISO-8859-3
ISO-8859-4
ISO-8859-5
ISO-8859-6
ISO-8859-7
ISO-8859-8
ISO-8859-9
JIS_X0201
JIS_X0212-1990
KOI8-R
KOI8-U
Shift_JIS
TIS-620
US-ASCII
UTF-16
UTF-16BE
UTF-16LE
UTF-32
UTF-32BE
UTF-32LE
UTF-8
UTF8
windows-1250
windows-1251
windows-1252
windows-1253
windows-1254
windows-1255
windows-1256
windows-1257
windows-1258
windows-31j
x-Big5-HKSCS-2001
x-Big5-Solaris
x-COMPOUND_TEXT
x-euc-jp-linux
x-EUC-TW
x-eucJP-Open
x-IBM1006
x-IBM1025
x-IBM1046
x-IBM1097
x-IBM1098
x-IBM1112
x-IBM1122
x-IBM1123
x-IBM1124
x-IBM1166
x-IBM1364
x-IBM1381
x-IBM1383
x-IBM300
x-IBM33722
x-IBM737
x-IBM833
x-IBM834
x-IBM856
x-IBM874
x-IBM875
x-IBM921
x-IBM922
x-IBM930
x-IBM933
x-IBM935
x-IBM937
x-IBM939
x-IBM942
x-IBM942C
x-IBM943
x-IBM943C
x-IBM948
x-IBM949
x-IBM949C
x-IBM950
x-IBM964
x-IBM970
x-ISCII91
x-ISO-2022-CN-CNS
x-ISO-2022-CN-GB
x-iso-8859-11
x-JIS0208
x-JISAutoDetect
x-Johab
x-MacArabic
x-MacCentralEurope
x-MacCroatian
x-MacCyrillic
x-MacDingbat
x-MacGreek
x-MacHebrew
x-MacIceland
x-MacRoman
x-MacRomania
x-MacSymbol
x-MacThai
x-MacTurkish
x-MacUkraine
x-MS932_0213
x-MS950-HKSCS
x-MS950-HKSCS-XP
x-mswin-936
x-PCK
x-SJIS_0213
x-UTF-16LE-BOM
X-UTF-32BE-BOM
X-UTF-32LE-BOM
x-windows-50220
x-windows-50221
x-windows-874
x-windows-949
x-windows-950
x-windows-iso2022jp
Java Detail
-----------
java.home= /u00/app/product/jdk181/jre
java.vendor= Oracle Corporation
java.vendor.url= http://java.oracle.com/
java.version= 1.8.0_181
------------------------------------------------------------------------------------------------------------------------------------------------------
os.arch= amd64
os.name= Linux
os.version= 4.1.12-61.1.18.el7uek.x86_64
path.separator= :
file.separator= /
line.separator=

user.dir= /home/oracle
user.home= /home/oracle
user.name= oracle
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_HOME=/home/oracle/sqlcl
------------------------------------------------------------------------------------------------------------------------------------------------------
Classpath
/u00/app/product/12.2/jdbc/lib/ojdbc8.jar
/u00/app/product/12.2/ojdbc8.jar
/u00/app/product/12.2/jdbc/lib/ojdbc7.jar
/u00/app/product/12.2/ojdbc7.jar
/u00/app/product/12.2/jdbc/lib/ojdbc6.jar
/u00/app/product/12.2/ojdbc6.jar
$SQL_HOME/lib/drivers/*
$SQL_HOME/lib/ext/*
$SQL_HOME/../rdbms/jlib/xdb6.jar
$SQL_HOME/../jdbc/lib/ojdbc8.jar
$SQL_HOME/../jlib/orai18n-utility.jar
$SQL_HOME/../jlib/orai18n-mapping.jar
$SQL_HOME/../jlib/orai18n.jar
$SQL_HOME/../modules/oracle.xdk/xmlparserv2.jar
$SQL_HOME/lib/javax.json.jar
$SQL_HOME/lib/xmlparserv2.jar
$SQL_HOME/lib/commons-logging.jar
$SQL_HOME/lib/orai18n.jar
$SQL_HOME/lib/xmlparserv2-sans-jaxp-services.jar
$SQL_HOME/lib/orai18n-collation.jar
$SQL_HOME/lib/jackson-databind.jar
$SQL_HOME/lib/xdb6.jar
$SQL_HOME/lib/jackson-core.jar
$SQL_HOME/lib/stringtemplate.jar
$SQL_HOME/lib/oraclepki.jar
$SQL_HOME/lib/jackson-annotations.jar
$SQL_HOME/lib/osdt_core.jar
$SQL_HOME/lib/httpmime.jar
$SQL_HOME/lib/osdt_cert.jar
$SQL_HOME/lib/httpcore.jar
$SQL_HOME/lib/orajsoda.jar
$SQL_HOME/lib/httpclient.jar
$SQL_HOME/lib/orai18n-utility.jar
$SQL_HOME/lib/ojdbc8.jar
$SQL_HOME/lib/dbtools-sqlcl.jar
$SQL_HOME/lib/dbtools-net.jar
$SQL_HOME/lib/dbtools-http.jar
$SQL_HOME/lib/dbtools-common.jar
$SQL_HOME/lib/orai18n-servlet.jar
$SQL_HOME/lib/jsch.jar
$SQL_HOME/lib/commons-codec.jar
$SQL_HOME/lib/jdbcrest.jar
$SQL_HOME/lib/orai18n-mapping.jar
$SQL_HOME/lib/jline.jar
$SQL_HOME/lib/antlr-runtime.jar
$SQL_HOME/lib/jansi.jar

------------------------------------------------------------------------------------------------------------------------------------------------------
/home/oracle/sqlcl
LDAPCON : Default
net: ON
noverwrite: WARN

SQLcl unsupported SQL*Plus command: show SECUREDCOL SPPARAMETER SQLDEV SQLDEV2 SQLFORMAT SQLPATH SQLPLUSCOMPAT SYSTEMOUT TABLE TOPLEVEL UFI URL XMLFORMAT XQUERY
securedcol is OFF
SQL Format : Default
SQLPATH : /home/oracle/:.
sqlpluscompatibility 12.2.0
systemout "true"
Toplevel TRUE
url top_base_url "" base_url "" node_url (Parent) "file:/home/oracle"
xmlformat is OFF
xquery BASEURI "" CONTEXT "" NODE DEFAULT ORDERING DEFAULT

Show All+ (Multi Line):
========================================

CONNECTION:
========================================
CONNECTION:
SYS@jdbc:oracle:oci8:@mos2pdb1 AS SYSDBA
CONNECTION_IDENTIFIER:
mos2pdb1
CONNECTION_DB_VERSION:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
NOLOG:
false
PRELIMAUTH:
false

DDL:
========================================
STORAGE : ON
INHERIT : ON
SQLTERMINATOR : ON
OID : ON
SPECIFICATION : ON
TABLESPACE : ON
SIZE_BYTE_KEYWORD : ON
PRETTY : ON
REF_CONSTRAINTS : ON
FORCE : ON
PARTITIONING : ON
CONSTRAINTS : ON
INSERT : ON
BODY : ON
CONSTRAINTS_AS_ALTER : ON
SEGMENT_ATTRIBUTES : ON

DEFINES:
========================================
DEFINE _DATE = "30-AUG-18" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "mos2pdb1" (CHAR)
DEFINE _USER = "SYS" (CHAR)
DEFINE _PRIVILEGE = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1802000000" (CHAR)
DEFINE _EDITOR = "vi" (CHAR)
DEFINE _O_VERSION = "Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production" (CHAR)
DEFINE _O_RELEASE = "1202000000" (CHAR)
DEFINE _PWD = "/home/oracle" (CHAR)

INSTANCE:
========================================
Instance "local"

INTERNALERRORS:
========================================
sqldev.error "false"
sqldev.error.any.in "false"
script.runner.autocommit.errorflag ""
sqldev.last.err.message ""
sqldev.last.err.message.forsqlcode ""

JDBC:
========================================
-- Database Info --
Database Product Name: Oracle
Database Product Version: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Database Major Version: 12
Database Minor Version: 2
-- Driver Info --
Driver Name: Oracle JDBC driver
Driver Version: 12.2.0.1.0
Driver Major Version: 12
Driver Minor Version: 2
Driver URL: jdbc:oracle:oci8:@mos2pdb1
Driver Location:
resource: oracle/jdbc/OracleDriver.class
jar: /u00/app/product/12.2/jdbc/lib/ojdbc8.jar
JarSize: 4036257
JarDate: Tue Dec 13 16:39:52 EST 2016
resourceSize: 2604
resourceDate: Tue Dec 13 08:39:48 EST 2016

LOGIN:
========================================
Glogin possible locations
--------------------
/home/oracle/sqlcl/
/u00/app/product/12.2/sqlplus/admin/

glogin.sql
---------------------
/u00/app/product/12.2/sqlplus/admin/glogin.sql

Login possible locations
--------------------
/home/oracle/sqlcl/
./

NLS:
========================================
DB_TIMEZONE +00:00
NLS_CALENDAR GREGORIAN
NLS_CHARACTERSET AL32UTF8
NLS_COMP BINARY
NLS_CURRENCY $
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_DUAL_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_LANGUAGE AMERICAN
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_NUMERIC_CHARACTERS .,
NLS_SORT BINARY
NLS_TERRITORY AMERICA
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
SESSION_TIMEZONE America/Toronto
SESSION_TIMEZONE_OFFSET -04:00

PDBS:
========================================

CON\_ID CON\_NAME                       OPEN MODE  RESTRICTED  

---------- ------------------------------ ---------- ----------
3 PDB1 READ WRITE NO

SGA:
========================================
Total System Global Area 1241513984 bytes
Fixed Size 8792248 bytes
Variable Size 872417096 bytes
Database Buffers 352321536 bytes
Redo Buffers 7983104 bytes

Thank you

1 - 6
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jan 18 2010
Added on Oct 13 2009
25 comments
2,422 views