OraFAQ Forum: SQL & PL/SQL » SQL with group by

Home » SQL & PL/SQL » SQL & PL/SQL » SQL with group by (Oracle SQL 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQL with group by [message #643275]

Fri, 02 October 2015 10:43

ramagirisrinivas
Messages: 6
Registered: September 2006

Junior Member

I have table called test and table data per below

SNO startdate enddate amount
10000 10/1/2001 9/30/2003 10.34
10000 10/1/2003 9/30/2005 15.89
10000 10/1/2005 9/30/2007 15.89
10000 10/1/2007 9/30/2013 10.34
10000 10/1/2013 11/30/2013 15.89
10000 12/1/2013 12/31/4000 27

I want the results in this way.

SNO startdate enddate amount
10000 10/1/2001 9/30/2003 10.34
10000 10/1/2003 9/30/2007 15.89
10000 10/1/2007 9/30/2013 10.34
10000 10/1/2013 11/30/2013 15.89
10000 12/1/2013 12/31/4000 27

Below query not giving the right results. please any help with this query only

select sno,min(startdate),max(endate),amount
from TEST
group by sno,amount

Report message to a moderator

Re: SQL with group by [message #643279 is a reply to message #643275]

Fri, 02 October 2015 10:55

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Welcome to this forum.

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

Report message to a moderator

Re: SQL with group by [message #643280 is a reply to message #643275]

Fri, 02 October 2015 11:05

ramagirisrinivas
Messages: 6
Registered: September 2006

Junior Member

Results expected as per below

SNO startdate enddate amount
10000 10/1/2001 9/30/2003 10.34
10000 10/1/2003 9/30/2007 15.89
10000 10/1/2007 9/30/2013 10.34
10000 10/1/2013 11/30/2013 15.89
10000 12/1/2013 12/31/4000 27

Report message to a moderator

Re: SQL with group by [message #643281 is a reply to message #643280]

Fri, 02 October 2015 11:11

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

ramagirisrinivas wrote on Fri, 02 October 2015 09:05

Results expected as per below

SNO     startdate       enddate         amount
10000	10/1/2001	9/30/2003	10.34
10000	10/1/2003	9/30/2007	15.89
10000	10/1/2007	9/30/2013	10.34
10000	10/1/2013	11/30/2013	15.89
10000	12/1/2013	12/31/4000	27

Why are above expected results?
How & why is the input transformed into the output?

Report message to a moderator

Re: SQL with group by [message #643282 is a reply to message #643281]

Fri, 02 October 2015 11:32

ramagirisrinivas
Messages: 6
Registered: September 2006

Junior Member

if look at the input table in the original post, there are 6 rows and output only 5 rows.

There two rows transformed into one rows because the amount and sno are same and there is no overlap in the dates.
SNO startdate enddate amount
10000 10/1/2003 9/30/2005 15.89
10000 10/1/2005 9/30/2007 15.89

Out put

SNO startdate enddate amount
10000 10/1/2003 9/30/2007 15.89

Report message to a moderator

Re: SQL with group by [message #643283 is a reply to message #643282]

Fri, 02 October 2015 12:09

ramagirisrinivas
Messages: 6
Registered: September 2006

Junior Member

I don't want have duplicate records (key is SNO and amount). if you look at the below records, kind of duplicate. I can create one record min(start_date) and max(enddate) when there is no break.
10/1/2005 started on second row when it ended on 9/30/2005

10000 10/1/2003 9/30/2005 15.89
10000 10/1/2005 9/30/2007 15.89

one record
10000 10/1/2003 9/30/2007 15.89

Report message to a moderator

Re: SQL with group by [message #643284 is a reply to message #643283]

Fri, 02 October 2015 12:11

ramagirisrinivas
Messages: 6
Registered: September 2006

Junior Member

New to this forum, how did you format result set in your reply?. That is nice. not able do it. put it in [code]?

Report message to a moderator

Re: SQL with group by [message #643285 is a reply to message #643284]

Fri, 02 October 2015 12:24

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Report message to a moderator

Re: SQL with group by [message #643286 is a reply to message #643285]

Fri, 02 October 2015 13:12

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

What should result from inputs like below & why?

SNO     startdate       enddate         amount
10000	10/1/2001	9/29/2003	10.34
10000	10/1/2003	9/30/2007	15.89

SNO     startdate       enddate         amount
10005	10/1/2001	9/30/2003	10.34
10005	11/1/2002	9/30/2002	15.89

SNO     startdate       enddate         amount
10007	10/1/2001	11/30/2003	10.34
10007	10/1/2003	9/30/2005	15.89

Report message to a moderator

Re: SQL with group by [message #643289 is a reply to message #643285]

Fri, 02 October 2015 13:30

ramagirisrinivas
Messages: 6
Registered: September 2006

Junior Member

Please see the working session


CREATE TABLE TEST1
(
   sno          VARCHAR2 (32),
   start_date   DATE,
   end_date      DATE,
   amount       NUMBER
);


Insert into TEST1
   (SNO,START_DATE,END_DATE,AMOUNT)
 Values
   ('10000', TO_DATE('10/01/2001', 'MM/DD/YYYY'), TO_DATE('09/30/2003', 'MM/DD/YYYY'), 10.34);
Insert into TEST1
   (SNO,START_DATE,END_DATE,AMOUNT)
 Values
   ('10000', TO_DATE('10/01/2003', 'MM/DD/YYYY'), TO_DATE('09/30/2005', 'MM/DD/YYYY'), 15.89);
Insert into TEST1
   (SNO,START_DATE,END_DATE,AMOUNT)
 Values
   ('10000', TO_DATE('10/01/2005', 'MM/DD/YYYY'), TO_DATE('09/30/2007', 'MM/DD/YYYY'), 15.89);
Insert into TEST1
   (SNO,START_DATE,END_DATE,AMOUNT)
 Values
   ('10000', TO_DATE('10/01/2013', 'MM/DD/YYYY'), TO_DATE('11/30/2013', 'MM/DD/YYYY'), 15.89);
Insert into TEST1
   (SNO,START_DATE,END_DATE,AMOUNT)
 Values
   ('10000', TO_DATE('12/01/2013', 'MM/DD/YYYY'), TO_DATE('12/31/4000', 'MM/DD/YYYY'), 27);
Insert into TEST1
   (SNO,START_DATE,END_DATE,AMOUNT)
 Values
   ('10000', TO_DATE('10/01/2007', 'MM/DD/YYYY'), TO_DATE('09/30/2013', 'MM/DD/YYYY'), 10.34);
   
   
   Insert into TEST1
   (SNO,START_DATE,END_DATE,AMOUNT)
 Values
   ('10001', TO_DATE('10/01/2007', 'MM/DD/YYYY'), TO_DATE('12/31/4000', 'MM/DD/YYYY'), 27);
   
   
   Insert into TEST1
   (SNO,START_DATE,END_DATE,AMOUNT)
 Values
   ('10002', TO_DATE('10/01/2007', 'MM/DD/YYYY'), TO_DATE('8/31/2015', 'MM/DD/YYYY'), 35);
   
    Insert into TEST1
   (SNO,START_DATE,END_DATE,AMOUNT)
 Values
   ('10002', TO_DATE('9/01/2015', 'MM/DD/YYYY'), TO_DATE('12/31/4000', 'MM/DD/YYYY'), 100);
   
   
    Insert into TEST1
   (SNO,START_DATE,END_DATE,AMOUNT)
 Values
   ('10003', TO_DATE('10/01/2007', 'MM/DD/YYYY'), TO_DATE('8/31/2015', 'MM/DD/YYYY'), 35);
   
    Insert into TEST1
   (SNO,START_DATE,END_DATE,AMOUNT)
 Values
   ('10003', TO_DATE('9/01/2015', 'MM/DD/YYYY'), TO_DATE('12/31/4000', 'MM/DD/YYYY'), 35);
   
   
COMMIT;


SELECT * FROM TEST1 ORDER BY 1,2;

-- table data
SNO     STAR_DATE       END_DATE        AMOUNT
10000	10/1/2001	9/30/2003	10.34
10000	10/1/2003	9/30/2005	15.89
10000	10/1/2005	9/30/2007	15.89
10000	10/1/2007	9/30/2013	10.34
10000	10/1/2013	11/30/2013	15.89
10000	12/1/2013	12/31/4000	27
10001	10/1/2007	12/31/4000	27
10002	10/1/2007	8/31/2015	35
10002	9/1/2015	12/31/4000	100
10003	10/1/2007	8/31/2015	35
10003	9/1/2015	12/31/4000	35

--Expected Result

SNO     STAR_DATE       END_DATE        AMOUNT
10000	10/1/2001	9/30/2003	10.34
10000	10/1/2003	9/30/2007	15.89
10000	10/1/2007	9/30/2013	10.34
10000	10/1/2013	11/30/2013	15.89
10000	12/1/2013	12/31/4000	27
10001	10/1/2007	12/31/4000	27
10002	10/1/2007	8/31/2015	35
10002	9/1/2015	12/31/4000	100
10003	10/1/2007	12/31/4000	35

The key here SNO and Amount columns. for example for this SNO : 10003 records amount are same and the start date and end date have tow records and but the expected result in one row. took min start date and max end date

Report message to a moderator

Re: SQL with group by [message #643290 is a reply to message #643289]

Fri, 02 October 2015 13:36

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

SQL> select sno, min(start_date) start_date, max(end_date) end_date, amount
  2  from test1
  3  group by sno, amount
  4  order by 1, 2
  5  /
SNO                              START_DATE  END_DATE        AMOUNT
-------------------------------- ----------- ----------- ----------
10000                            01-OCT-2001 30-SEP-2013      10.34
10000                            01-OCT-2003 30-NOV-2013      15.89
10000                            01-DEC-2013 31-DEC-4000         27
10001                            01-OCT-2007 31-DEC-4000         27
10002                            01-OCT-2007 31-AUG-2015         35
10002                            01-SEP-2015 31-DEC-4000        100
10003                            01-OCT-2007 31-DEC-4000         35

7 rows selected.

Report message to a moderator

Re: SQL with group by [message #643337 is a reply to message #643289]

Mon, 05 October 2015 08:47

sandeep_orafaq
Messages: 88
Registered: September 2014

Member

ramagirisrinivas wrote on Fri, 02 October 2015 13:30

Please see the working session


CREATE TABLE TEST1
(
   sno          VARCHAR2 (32),
   start_date   DATE,
   end_date      DATE,
   amount       NUMBER
);


Insert into TEST1
   (SNO,START_DATE,END_DATE,AMOUNT)
 Values
   ('10000', TO_DATE('10/01/2001', 'MM/DD/YYYY'), TO_DATE('09/30/2003', 'MM/DD/YYYY'), 10.34);
Insert into TEST1
   (SNO,START_DATE,END_DATE,AMOUNT)
 Values
   ('10000', TO_DATE('10/01/2003', 'MM/DD/YYYY'), TO_DATE('09/30/2005', 'MM/DD/YYYY'), 15.89);
Insert into TEST1
   (SNO,START_DATE,END_DATE,AMOUNT)
 Values
   ('10000', TO_DATE('10/01/2005', 'MM/DD/YYYY'), TO_DATE('09/30/2007', 'MM/DD/YYYY'), 15.89);
Insert into TEST1
   (SNO,START_DATE,END_DATE,AMOUNT)
 Values
   ('10000', TO_DATE('10/01/2013', 'MM/DD/YYYY'), TO_DATE('11/30/2013', 'MM/DD/YYYY'), 15.89);
Insert into TEST1
   (SNO,START_DATE,END_DATE,AMOUNT)
 Values
   ('10000', TO_DATE('12/01/2013', 'MM/DD/YYYY'), TO_DATE('12/31/4000', 'MM/DD/YYYY'), 27);
Insert into TEST1
   (SNO,START_DATE,END_DATE,AMOUNT)
 Values
   ('10000', TO_DATE('10/01/2007', 'MM/DD/YYYY'), TO_DATE('09/30/2013', 'MM/DD/YYYY'), 10.34);
   
   
   Insert into TEST1
   (SNO,START_DATE,END_DATE,AMOUNT)
 Values
   ('10001', TO_DATE('10/01/2007', 'MM/DD/YYYY'), TO_DATE('12/31/4000', 'MM/DD/YYYY'), 27);
   
   
   Insert into TEST1
   (SNO,START_DATE,END_DATE,AMOUNT)
 Values
   ('10002', TO_DATE('10/01/2007', 'MM/DD/YYYY'), TO_DATE('8/31/2015', 'MM/DD/YYYY'), 35);
   
    Insert into TEST1
   (SNO,START_DATE,END_DATE,AMOUNT)
 Values
   ('10002', TO_DATE('9/01/2015', 'MM/DD/YYYY'), TO_DATE('12/31/4000', 'MM/DD/YYYY'), 100);
   
   
    Insert into TEST1
   (SNO,START_DATE,END_DATE,AMOUNT)
 Values
   ('10003', TO_DATE('10/01/2007', 'MM/DD/YYYY'), TO_DATE('8/31/2015', 'MM/DD/YYYY'), 35);
   
    Insert into TEST1
   (SNO,START_DATE,END_DATE,AMOUNT)
 Values
   ('10003', TO_DATE('9/01/2015', 'MM/DD/YYYY'), TO_DATE('12/31/4000', 'MM/DD/YYYY'), 35);
   
   
COMMIT;


SELECT * FROM TEST1 ORDER BY 1,2;

-- table data
SNO     STAR_DATE       END_DATE        AMOUNT
10000	10/1/2001	9/30/2003	10.34
10000	10/1/2003	9/30/2005	15.89
10000	10/1/2005	9/30/2007	15.89
10000	10/1/2007	9/30/2013	10.34
10000	10/1/2013	11/30/2013	15.89
10000	12/1/2013	12/31/4000	27
10001	10/1/2007	12/31/4000	27
10002	10/1/2007	8/31/2015	35
10002	9/1/2015	12/31/4000	100
10003	10/1/2007	8/31/2015	35
10003	9/1/2015	12/31/4000	35

--Expected Result

SNO     STAR_DATE       END_DATE        AMOUNT
10000	10/1/2001	9/30/2003	10.34
10000	10/1/2003	9/30/2007	15.89
10000	10/1/2007	9/30/2013	10.34
10000	10/1/2013	11/30/2013	15.89
10000	12/1/2013	12/31/4000	27
10001	10/1/2007	12/31/4000	27
10002	10/1/2007	8/31/2015	35
10002	9/1/2015	12/31/4000	100
10003	10/1/2007	12/31/4000	35

The key here SNO and Amount columns. for example for this SNO : 10003 records amount are same and the start date and end date have tow records and but the expected result in one row. took min start date and max end date

Are you sure about your output? It does not go according to your wordings.

Report message to a moderator

Re: SQL with group by [message #643338 is a reply to message #643337]

Mon, 05 October 2015 08:52

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

I agree, this is why I gave a query according to the wordings and not to the given result.
If the given result is correct then OP has to change his wordings and post a clear and correct specification.

[Edit: english]

[Updated on: Mon, 05 October 2015 08:58]

Report message to a moderator

Previous Topic:	Understand Difference between queries
Next Topic:	What is the Difference b/w Up datable Views and Non-Up datable Views their uses

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 20:39:41 CDT 2024