I have a table with the serial number of each product, whether it is in stock (1- in stock, 0- not in stock), the level of revenue from the product and the level of expenses from the product in the store. I would like to write a query that counts all customer pairs (without duplication of the same pair), that the expense difference between them is less than NIS 1,000 and both are in stock or both are out of stock. Show the average income gap (approximately) of all pairs, how many such pairs are in stock And how much is not in stock.

Sample table:

Unfortunately I am lost and unable to find a solution to my problem. I was thinking of creating subqueries but could not find a suitable solution

The result should show something like this(Please do not refer to this data for illustration):

In addition it is very important that the count be done without duplicates of the same pair

CodePudding user response：

I have solved it in stored procedure.

Table creation:

CREATE TABLE A(serial INT(11), Is_in_stock INT(11), Revenu_from_the_product INT(11), Expenses_from_the_product INT(11));

Data insertion:

INSERT INTO A (serial,Is_in_stock,Revenu_from_the_product,Expenses_from_the_product) VALUES
  (1,1,27627,57661),
  (2,0,48330,20686),
  (3,0,26010,861  ),
  (4,1,22798,37771),
  (5,0,24606,8905 ),
  (6,1,48311,6433 ),
  (7,0,29929,6278 ),
  (8,0,24254,8590 );

Query:

BEGIN
    DECLARE finished INTEGER DEFAULT 0;
    DECLARE prev_exp int(11) DEFAULT 0;
  DECLARE prev_in_stock int(11) DEFAULT 0;
  DECLARE curr_Is_in_stock int(11) DEFAULT 0;
  DECLARE curr_Expenses_from_the_product int(11) DEFAULT 0;
    DECLARE duplications_counter int(11) DEFAULT 0;
    

    -- declare cursor for relevant fields
    DEClARE curs
        CURSOR FOR 
            SELECT A.Is_in_stock,A.Expenses_from_the_product FROM A ORDER BY A.Expenses_from_the_product DESC;

    -- declare NOT FOUND handler
    DECLARE CONTINUE HANDLER 
        FOR NOT FOUND SET finished = 1;

    OPEN curs;

    getRow: LOOP
        FETCH curs INTO curr_Is_in_stock,curr_Expenses_from_the_product;
        IF finished = 1 THEN 
            LEAVE getRow;
        END IF;
        IF prev_exp - curr_Expenses_from_the_product < 1000 AND prev_in_stock - curr_Is_in_stock = 0 THEN
                SET duplications_counter = duplications_counter 1;
        END IF;
    END LOOP getRow;
    CLOSE curs;
-- return the counter
SELECT duplications_counter;
END

Result:

Counter: 5

CodePudding user response：

We can do this with two queries, without a procedure or user defined function

CREATE TABLE products(serial INT, Instock INT, Revenu INT, Expenses INT);

INSERT INTO products VALUES
  (1,1,27627,57661),
  (2,0,48330,20686),
  (3,0,26010,861  ),
  (4,1,22798,37771),
  (5,0,24606,8905 ),
  (6,1,48311,6433 ),
  (7,0,29929,6278 ),
  (8,0,24254,8590 );

✓

✓

SELECT a.serial,b.serial from 
products a
join products b
on abs(a.expenses-b.expenses)<1000
where a.serial<b.serial
and a.instock=b.instock

serial | serial
-----: | -----:
     5 |      8

select count(a.expenses) 'number of pairs',
  avg(abs(a.expenses-b.expenses)) 'average difference',
  sum(case when a.instock=1 and b.instock=1 then 1 else 0 end) pairsInstock,
  sum(case when a.instock=0 and b.instock=0 then 1 else 0 end) pairsneitherStock,
  sum(case when (a.instock b.instock)=1 then 1 else 0 end ) oneInStock
from products a
cross join products b
where a.serial < b.serial;

number of pairs | average difference | pairsInstock | pairsneitherStock | oneInStock
--------------: | -----------------: | -----------: | ----------------: | ---------:
             28 |         21362.1071 |            3 |                10 |         15

db<>fiddle here