PostgreSQL simple query

Question

My problem is to write a query (PostgreSQL) to get a 'propno' where 'newsname' is not equal 'NYT' (the answer is 40).

       newsname       | propno | dateadvert | cost 
----------------------+--------+------------+------
 NYT                  |     30 | 2018-01-01 | 10.2
 NYT                  |     30 | 2018-01-10 | 15.2
 NYT                  |     10 | 2018-01-02 | 20.2
 NYT                  |     20 | 2018-02-01 | 10.2
 Guardian             |     40 | 2018-02-10 |  100
 Guardian             |     10 | 2018-01-02 | 13.2
 Guardian             |     30 | 2018-01-10 | 10.8

Thanks for help

Do you want the maximum propno? Otherwise the result was 40, 10 and 30. — Steffen Bobek
– Steffen Bobek, Commented Dec 28, 2019 at 23:20
Why did you put in sqlite and mariadb tags if this is for postgres? — Shawn
– Shawn, Commented Dec 28, 2019 at 23:24
Nah, you see that NYT already has 10 and 30. I need to query which would print 40 because the 'NYT' does not have 40. — Nedas Jankauskas
– Nedas Jankauskas, Commented Dec 28, 2019 at 23:24

Gordon Linoff · Accepted Answer · 2019-12-28 23:25:50Z

1

You can use aggregation and having:

select propno
from t
group by propno
having count(*) filter (where newsname = 'NYT') = 0;

If you have a separate table with one row per propno, then I would recommend not exists:

select p.propno
from props p
where not exists (select 1
                  from t
                  where t.propno = p.propno and
                        t.newname = 'NYT'
                 );

answered Dec 28, 2019 at 23:25

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

DB Wizz · Accepted Answer · 2019-12-29 00:18:17Z

0

You can use exists:

select propno
from props p
where not exists (
  select 1
  from props t
  where t.propno = p.propno
  and t.newname = 'NYT'
);

answered Dec 29, 2019 at 0:18

DB Wizz

271 gold badge1 silver badge4 bronze badges

Comments

Lukasz Szozda · Accepted Answer · 2019-12-29 07:42:10Z

0

HAVING could be used to filter out 'NYT':

SELECT propno
FROM tab_name
GROUP BY propno
HAVING SUM(newsname='NYT')=0;

answered Dec 29, 2019 at 7:42

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Collectives™ on Stack Overflow

PostgreSQL simple query

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related