Home » SQL & PL/SQL » SQL & PL/SQL » One row for multiple rows based on a condition
One row for multiple rows based on a condition [message #683022] Wed, 25 November 2020 17:08 Go to next message
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
Re: One row for multiple rows based on a condition [message #683023 is a reply to message #683022] Thu, 26 November 2020 00:04 Go to previous messageGo to next message
Michel Cadot
Messages: 67648
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 Go to previous message
Michel Cadot
Messages: 67648
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
Previous Topic: Day book format
Next Topic: Table joining with two columns sharing a foreign key
Goto Forum:
  


Current Time: Fri Jan 22 11:06:32 CST 2021