Check if values are the same for each row over a critiera in another column-CodePudding

DB-Fiddle

CREATE TABLE campaigns (
    id SERIAL PRIMARY KEY,
    campaign VARCHAR,
    supplier VARCHAR,
    plan_quantity DECIMAL
);

INSERT INTO campaigns
(campaign, supplier, plan_quantity)
VALUES 
('C001', 'supplier_a', '500'),
('C001', 'supplier_a', '500'),
('C001', 'supplier_b', '500'),

('C002', 'supplier_a', '600'),
('C002', 'supplier_b', '700'),

('C003', 'supplier_c', '100'),
('C003', 'supplier_c', '100'),

('C004', 'supplier_a', '900'),
('C004', 'supplier_c', '800'),
('C004', 'supplier_d', '250'),
('C004', 'supplier_d', '250');

Expected Result:

campaign  |   supplier   |   plan_quantity   |     check      |
----------|--------------|-------------------|----------------|-------
  C001    |  supplier_a  |        500        |      same      |
  C001    |  supplier_a  |        500        |      same      |
  C001    |  supplier_b  |        500        |      same      |
----------|--------------|-------------------|----------------|-------
  C002    |  supplier_a  |        600        |    different   |
  C002    |  supplier_b  |        700        |    different   | 
----------|--------------|-------------------|----------------|-------
  C003    |  supplier_c  |        100        |      same      |
  C003    |  supplier_c  |        100        |      same      |
----------|--------------|-------------------|----------------|-------
  C004    |  supplier_a  |        900        |    different   |
  C004    |  supplier_c  |        800        |    different   |
  C004    |  supplier_d  |        250        |    different   |
  C004    |  supplier_d  |        250        |    different   |

In column check in the results I want see if the plan_quantity for each supplier per campaign is the same.
If yes then it should be written same if not it should be written different.

SELECT
campaign AS campaign,
supplier AS supplier,
plan_quantity AS plan_quantity,
(CASE WHEN plan_quantity for each supplier per campaign is the same THEN 'same' else 'different' END) AS check
FROM campaigns
ORDER BY 1,2,3;

I have no clue what function I need to make it work.
Do you have any idea?

CodePudding user response：

You can use window analytic functions to check the values:

select *, 
  case when 
      Min(plan_quantity) over(partition by campaign) 
        = Max(plan_quantity) over(partition by campaign) 
    then 'same' else 'different'
    end as "Check"
from campaigns

CodePudding user response：

You can use window max and min

SELECT
  campaign AS campaign,
  supplier AS supplier,
  plan_quantity AS plan_quantity,
  CASE WHEN max(plan_quantity) over(partition by supplier, campaign) = min(plan_quantity) over(partition by supplier, campaign) THEN 'same' ELSE 'different' END AS check
FROM campaigns
ORDER BY 1,2,3;

CodePudding user response：

You can find out which campaign has an unique plan_quantity by grouping by campaign and simply count distint plan_quantity

SELECT
    campaign AS campaign
FROM campaigns
GROUP BY campaign
HAVING COUNT(DISTINCT plan_quantity) = 1

And with that you can do what you want. With a join for exemple :

SELECT
    campaigns.campaign,
    supplier,
    plan_quantity,
    CASE WHEN grouping IS NOT NULL THEN 'same' ELSE 'different' END as check
FROM campaigns
    LEFT JOIN (
        -- Get all campaign with same plan_quantity
        SELECT
            campaign
        FROM campaigns
        GROUP BY campaign
        HAVING COUNT(DISTINCT plan_quantity) = 1
    ) as grouping
ON campaigns.campaign = grouping.campaign