Home » SQL & PL/SQL » SQL & PL/SQL » Help Grouping by
Help Grouping by [message #670211] Mon, 18 June 2018 11:13 Go to next message
akull
Messages: 44
Registered: July 2012
Location: Argentina
Member
Hi Experts,

It's been a while since I post here but today I am facing an issue that is driving me crazy.

I have the following data model;

CREATE TABLE ORDERS
(
    PROYECT VARCHAR2(50),
    STATUS  VARCHAR2(10)
)

INSERT INTO ORDERS
values
('PY1','Complete')


INSERT INTO ORDERS
values
('PY1',null)


INSERT INTO ORDERS
values
('PY2','Complete')


INSERT INTO ORDERS
values
('PY2','Complete')

As you can see the values Proyect could have multiple status, I need to write a query that groups by both of them and only shows the ones that are fully completed, meaning that if any project has a status completed and the other one null, I have to discard it.

What I've tried so far is a simple group by

select count(*), status
from orders
where proyect = 'PY1'
group by status

but it does not solve my needs. Honestly I do not know where to start.

If someone could give a hint, It will be much appreciated.
Re: Help Grouping by [message #670212 is a reply to message #670211] Mon, 18 June 2018 11:21 Go to previous messageGo to next message
BlackSwan
Messages: 26649
Registered: January 2009
Location: SoCal
Senior Member
what is expected & desired results and why are these results correct?
Re: Help Grouping by [message #670213 is a reply to message #670211] Mon, 18 June 2018 11:24 Go to previous messageGo to next message
Michel Cadot
Messages: 66808
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Something like:
SQL> select PROYECT, count(*) from orders group by PROYECT having count(*)=count(decode(status,'Complete',status));
PROYECT                                              COUNT(*)
-------------------------------------------------- ----------
PY2                                                         2
Re: Help Grouping by [message #670214 is a reply to message #670212] Mon, 18 June 2018 11:24 Go to previous messageGo to next message
akull
Messages: 44
Registered: July 2012
Location: Argentina
Member
Hi BlackSwan,

The expected result is group by status where all of the statuses of one project are completed, status = 'Complete'. One proyect could have one records that shows complete and other(s) that are null, the last one I have to discard.

Re: Help Grouping by [message #670215 is a reply to message #670213] Mon, 18 June 2018 11:25 Go to previous messageGo to next message
Michel Cadot
Messages: 66808
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Or if status can only be either NULL or 'Complete' you can simplify it to:
SQL>  select PROYECT, count(*) from orders group by PROYECT having count(*)=count(status);
PROYECT                                              COUNT(*)
-------------------------------------------------- ----------
PY2                                                         2

[Updated on: Mon, 18 June 2018 11:26]

Report message to a moderator

Re: Help Grouping by [message #670216 is a reply to message #670214] Mon, 18 June 2018 11:29 Go to previous messageGo to next message
John Watson
Messages: 8138
Registered: January 2010
Location: Global Village
Senior Member
To get rid of PROYECTs where there is a NULL row,
orclx>
orclx> select proyect from orders where status='Complete'
  2  minus
  3  select  proyect from orders where status is null
  4  /

PROYECT
--------------------------------------------------
PY2

orclx>
Re: Help Grouping by [message #670217 is a reply to message #670216] Mon, 18 June 2018 11:33 Go to previous message
akull
Messages: 44
Registered: July 2012
Location: Argentina
Member
Thank you very very much guys!! It Worked!!
Previous Topic: divide text string into different columns
Next Topic: SQL: Substring folder name based on numeric value
Goto Forum:
  


Current Time: Wed Jan 29 03:48:08 CST 2020