SQL: how to get a subset from a subset that is subject to an "only" condition-CodePudding

I have a table named orders in a Postgres database (see Fiddle at http://sqlfiddle.com/#!17/ac4f9).

CREATE TABLE orders 
(
    user_id INTEGER, 
    order_id INTEGER, 
    order_date DATE, 
    price FLOAT, 
    product VARCHAR(255)
);

INSERT INTO orders(user_id, order_id, order_date, price, product)
VALUES
(1, 2, '2021-03-05', 15, 'books'),
(1, 13, '2022-03-07', 3, 'music'),
(1, 14, '2022-06-15', 900, 'travel'),
(1, 11, '2021-11-17', 25, 'books'),
(1, 16, '2022-08-03', 32, 'books'),
(2, 4, '2021-04-12', 4, 'music'),
(2, 7, '2021-06-29', 9, 'music'),
(2, 20, '2022-11-03', 8, 'music'),
(2, 22, '2022-11-07', 575, 'travel'),
(2, 24, '2022-11-20', 95, 'food'),
(3, 3, '2021-03-17', 25, 'books'),
(3, 5, '2021-06-01', 650, 'travel'),
(3, 17, '2022-08-17', 1200, 'travel'),
(3, 19, '2022-10-02', 6, 'music'),
(3, 23, '2022-11-08', 7, 'food'),
(4, 9, '2021-08-20', 3200, 'travel'),
(4, 10, '2021-10-29', 2750, 'travel'),
(4, 15, '2022-07-15', 1820, 'travel'),
(4, 21, '2022-11-05', 8000, 'travel'),
(4, 25, '2022-11-29', 2300, 'travel'),
(5, 1, '2021-01-04', 3, 'music'),
(5, 6, '2021-06-09', 820, 'travel'),
(5, 8, '2021-07-30', 19, 'books'),
(5, 12, '2021-12-10', 22, 'music'),
(5, 18, '2022-09-19', 20, 'books'),
(6, 26, '2023-01-09', 700, 'travel'),
(6, 27, '2023-01-23', 1900, 'travel');

From the list of users who have placed an order for the either the travel product OR the books product, I would like to get the subset of these users who have placed an order for ONLY the travel product.

The desired result set would be:

user_id    count_orders
-----------------------
4          5
6          2

How would I do this?

Thank you.

CodePudding user response：

select o.user_id, count(*) count_orders
from orders o
where not exists(select * from orders where product<>'travel' and user_id=o.user_id)
group by o.user_id

http://sqlfiddle.com/#!17/ac4f9/17

CodePudding user response：

Count all orders and travel orders first. Filter records with same count values.

With inline view: http://sqlfiddle.com/#!17/ac4f9/18/0

SELECT user_id, n_orders AS count_orders
FROM (
    SELECT user_id
        , COUNT(CASE WHEN product = 'travel' THEN 1 END) AS n_travels
        , COUNT(*) AS n_orders
    FROM orders
    GROUP BY user_id
) v
WHERE v.n_travels = v.n_orders

Using HAVING clause 1: http://sqlfiddle.com/#!17/ac4f9/22/0

SELECT user_id
    , COUNT(*) AS count_orders
FROM orders
GROUP BY user_id
HAVING COUNT(CASE WHEN product != 'travel' THEN 1 END) = COUNT(*)

Using HAVING clause 2: http://sqlfiddle.com/#!17/ac4f9/21/0

SELECT user_id
    , COUNT(*) AS count_orders
FROM orders
GROUP BY user_id
HAVING COUNT(CASE WHEN product != 'travel' THEN 1 END) = 0

CodePudding user response：

Using EXCEPT operation

select user_id, count(*)
from orders
where user_id in (
  select user_id from orders where product = 'travel'
  except
  select user_id from orders where product <> 'travel'
)
group by user_id
order by user_id