How to aggregate in the WHERE clause referencing the subquery from the FROM clause

Question

I got the following query so far:

SELECT q1.ID, Content, Volume, CustomerID, runtot
FROM (
  SELECT ID, Content, Volume, CustomerID,
    SUM(Volume) OVER (PARTITION BY CustomerID ORDER BY ID) AS runtot,
    ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY ID) AS rnum
  FROM multiqueue
  WHERE PublishedTS IS NULL
) AS q1
JOIN customers AS c1 ON q1.CustomerID=customers.ID
WHERE
  runtot < 2000 * c1.Priority / (SELECT SUM(Priority) FROM c1)
  OR rnum <= 1

It complains about a syntax error near SELECT SUM(Priority) FROM c1: neither c1, nor q1 table can be referenced there.

So the following doesn't work either for computing the sum:

(SELECT SUM(Priority) FROM customers WHERE ID IN q1.CustomerID)

What I'm trying to achieve is a sum of customers.Priority over just the customers selected in q1.

Could you please help me to fix the query if possible?

The DB is MariaDB v10.4.13 (you can also assume the latest version or even MySQL).

UPDATE: The following doesn't work either

SELECT q1.ID, Content, Volume, CustomerID, runtot
FROM (
  SELECT ID, Content, Volume, CustomerID,
    SUM(Volume) OVER (PARTITION BY CustomerID ORDER BY ID) AS runtot,
    ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY ID) AS rnum
  FROM multiqueue
  WHERE PublishedTS IS NULL
) AS q1
JOIN customers AS c1 ON q1.CustomerID=customers.ID
WHERE
  runtot < 2000 * c1.Priority / (SUM(Priority) OVER (PARTITION BY q1.CustomerID))
  OR rnum <= 1

MariaDB responds with error 4015: Window function is allowed only in SELECT list and ORDER BY clause.

UPDATE2: Table creation and population

CREATE TABLE `multiqueue` (
    `ID` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `CustomerID` BIGINT(20) NOT NULL,
    `Volume` INT(11) NOT NULL,
    `Content` MEDIUMTEXT NOT NULL COLLATE 'utf8mb4_unicode_ci',
    `PublishedTS` DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (`ID`) USING BTREE
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
;

CREATE TABLE `customers` (
    `ID` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `Priority` DOUBLE NOT NULL DEFAULT '1000',
    PRIMARY KEY (`ID`) USING BTREE
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
;

INSERT INTO multiqueue VALUES
  (1, 1, 100, 'Content1', NULL),
  (2, 1, 200, 'Content2', NULL),
  (3, 1, 300, 'Content3', NULL),
  (4, 1, 400, 'Content4', NULL),
  (5, 1, 500, 'Content5', NULL),
  (6, 2, 100, 'Content6', NULL),
  (7, 2, 200, 'Content7', NULL),
  (8, 2, 300, 'Content8', NULL),
  (9, 2, 400, 'Content9', NULL),
  (10, 2, 500, 'Content10', NULL),
  (11, 1, 600, 'Content11', NULL)

INSERT INTO customers VALUES
  (1, 1000),
  (2, 500),
  (3, 100000)

I would like that the query produces approximately twice as much volume for customer #1 as for customer #2, and the total volume over both customers is about 2000.

UPDATE3: for the above inputs, the output can be:

ID    | Content   | Volume | CustomerID | runtot
------------------------------------------------
1     | Content1  | 100    | 1          | 100
2     | Content2  | 200    | 1          | 300
3     | Content3  | 300    | 1          | 600
4     | Content4  | 400    | 1          | 1000
6     | Content6  | 100    | 2          | 100
7     | Content7  | 200    | 2          | 300
8     | Content8  | 300    | 2          | 600

As we can see, the query must have selected a total volume of 1600 over both customers, approximately maintaining the proportion 1000:500 while keeping the total volume below 2000 if possible when selecting at least one row for every customer.

Answer 1

Your own solution might be suboptimal but I am not sure there is a way to solve the problem elegantly and/or efficiently without at least some redundancy. I managed to avoid hitting the customers table more than once, but I still had to reference multiqueue twice. This is the query I ended up with:

SELECT
  q.ID,
  q.Content,
  q.Volume,
  q.CustomerID,
  q.runtot
FROM
  (
    SELECT
      ID, Content, Volume, CustomerID,
      SUM(Volume)  OVER (PARTITION BY CustomerID ORDER BY ID ASC) AS runtot,
      ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY ID ASC) AS rnum
    FROM
      multiqueue
    WHERE
      PublishedTS IS NULL
  ) AS q
  INNER JOIN
  (
    SELECT
      ci.ID, ci.Priority,
      SUM(ci.Priority) OVER () AS TotalPriority
    FROM
      customers AS ci
    WHERE
      EXISTS
      (
        SELECT * FROM multiqueue AS qi
        WHERE qi.PublishedTS IS NULL AND qi.CustomerID = ci.ID
      )
  ) AS c ON q.CustomerID = c.ID
WHERE
  q.runtot < 2000 * c.Priority / c.TotalPriority
  OR q.rnum <= 1
;

Basically, the join to customers is replaced with a join to a nested select from customers that incorporates both the SUM(Priority) calculation and the EXISTS check. The check makes sure only the customers represented in multiqueue are returned, and as a result, the Priority total is calculated across those customers only.

The rest of the logic is, I hope, the same as in your own query.

You can test my solution in this demo at db<>fiddle.uk.

Answer 2

SELECT q1.ID, Content, Volume, CustomerID, runtot
FROM (
  SELECT ID, Content, Volume, CustomerID,
    SUM(Volume) OVER (PARTITION BY CustomerID ORDER BY ID) AS runtot,
    ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY ID) AS rnum
  FROM multiqueue
  WHERE PublishedTS IS NULL
) AS q1
JOIN customers AS c1 ON q1.CustomerID=c1.ID
WHERE
  runtot < 2000 * Priority / (SELECT SUM(Priority) FROM customers WHERE ID = CustomerID)
  OR rnum <= 1

ID | Content   | Volume | CustomerID | runtot
-: | :-------- | -----: | ---------: | -----:
 1 | Content1  |    100 |          1 |    100
 2 | Content2  |    200 |          1 |    300
 3 | Content3  |    300 |          1 |    600
 4 | Content4  |    400 |          1 |   1000
 5 | Content5  |    500 |          1 |   1500
 6 | Content6  |    100 |          2 |    100
 7 | Content7  |    200 |          2 |    300
 8 | Content8  |    300 |          2 |    600
 9 | Content9  |    400 |          2 |   1000
10 | Content10 |    500 |          2 |   1500

SELECT q1.ID, Content, Volume, CustomerID, runtot
FROM (
  SELECT ID, Content, Volume, CustomerID,
    SUM(Volume) OVER (PARTITION BY CustomerID ORDER BY ID) AS runtot,
    ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY ID) AS rnum
  FROM multiqueue
  WHERE PublishedTS IS NULL
) AS q1
JOIN customers AS c1 ON q1.CustomerID=c1.ID
WHERE
  runtot < 2000 * Priority / (SELECT SUM(Priority) FROM customers)
  OR rnum <= 1

ID | Content  | Volume | CustomerID | runtot
-: | :------- | -----: | ---------: | -----:
 1 | Content1 |    100 |          1 |    100
 2 | Content2 |    200 |          1 |    300
 3 | Content3 |    300 |          1 |    600
 4 | Content4 |    400 |          1 |   1000
 6 | Content6 |    100 |          2 |    100
 7 | Content7 |    200 |          2 |    300
 8 | Content8 |    300 |          2 |    600

db<>fiddle here

Depending what information you want, you can select the max(ID) for every customerID(GROUP BY)

Answer 3

Below is a query that achieves the goal, though it might be far suboptimal:

SELECT p1.ID, Content, Volume, CustomerID, runtot
FROM (
  SELECT ID, Content, Volume, CustomerID,
    SUM(Volume) OVER (PARTITION BY CustomerID ORDER BY ID) AS runtot,
    ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY ID) AS rnum
  FROM multiqueue
  WHERE PublishedTS IS NULL
) AS p1
JOIN customers AS c1 ON p1.CustomerID=c1.ID
WHERE
  runtot < 2000 * c1.Priority / (
    SELECT SUM(Priority) FROM customers AS c2
    WHERE EXISTS (
      SELECT 1 FROM multiqueue AS p2
      WHERE p2.CustomerID=c2.ID
        AND p2.PublishedTS IS NULL
    )
  )
  OR rnum <= 1

Verified at dbfiddle.