Help Grouping by [message #670211] |
Mon, 18 June 2018 11:13 |
|
akull
Messages: 46 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 #670215 is a reply to message #670213] |
Mon, 18 June 2018 11:25 |
|
Michel Cadot
Messages: 68693 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 |
John Watson
Messages: 8951 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>
|
|
|
|