OraFAQ Forum: SQL & PL/SQL » One row for multiple rows based on a condition

Home » SQL & PL/SQL » SQL & PL/SQL » One row for multiple rows based on a condition

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

One row for multiple rows based on a condition [message #683022]

Wed, 25 November 2020 17:08

annacol
Messages: 9
Registered: April 2006
Location: Sri Lanka

Junior Member

create table sales(
sales_order_no number,
line_num number,
status varchar2(10));

INSERT INTO sales
VALUES (100,1,'PAID')

INSERT INTO sales
Values (100,2,'NOT PAID')

INSERT INTO sales
values (100,3,'PAID')

INSERT INTO sales
values (200,1,'PAID')

INSERT INTO sales
values (200,2,'PAID')

INSERT INTO sales
values(300,5,'NOT PAID')

INSERT INTO sales
values (300,10,'NOT PAID')

Logic - if the status column for all line_num is 'PAID' then the results should Y else N

result should look like be

salesOrderNo Status
100 N - as all lines are not paid so should display 'N'
200 Y - as all lines are paid
300 N - both lines are not paid

Thanks and regards
Anna

Report message to a moderator

Re: One row for multiple rows based on a condition [message #683023 is a reply to message #683022]

Thu, 26 November 2020 00:04

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

Senior Member
Account Moderator

Michel Cadot wrote on Thu, 18 June 2020 07:36

...
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

SQL> select sales_order_no,
  2         decode(count(decode(status,'PAID',status)),count(*),'Y','N') status
  3  from sales
  4  group by sales_order_no
  5  order by sales_order_no
  6  /
SALES_ORDER_NO S
-------------- -
           100 N
           200 Y
           300 N

3 rows selected.

[Updated on: Thu, 26 November 2020 00:05]

Report message to a moderator

Re: One row for multiple rows based on a condition [message #683024 is a reply to message #683023]

Thu, 26 November 2020 00:07

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

Senior Member
Account Moderator

Or, if STATUS can't be NULL (that is is defined as NOT NULL):

SQL> select sales_order_no,
  2         decode(count(nullif(status,'PAID')),0,'Y','N') status
  3  from sales
  4  group by sales_order_no
  5  order by sales_order_no
  6  /
SALES_ORDER_NO S
-------------- -
           100 N
           200 Y
           300 N

Report message to a moderator

Previous Topic:	Day book format
Next Topic:	Table joining with two columns sharing a foreign key

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Mar 23 06:19:54 CDT 2023