[SOLVED] Can I use SQL ROLLUP() instead of doing 2 request on DB for calculation total value for metrics in pagination?

Can I use SQL ROLLUP() instead of doing 2 request on DB for calculation total value for metrics in pagination?

I use SingleStore/MemSQL DB and try apply https://docs.singlestore.com/managed-service/en/reference/sql-reference/data-manipulation-language-dml/cube-and-rollup.html

CREATE TABLE sales(state VARCHAR(30), product_id INT, quantity INT);
INSERT sales VALUES
  ("Oregon", 1, 10), ("Washington", 1, 15), ("California", 1, 40),
  ("Oregon", 2, 15), ("Washington", 2, 25), ("California", 2, 70);

SELECT state, product_id,
       SUM(quantity) as quantity
FROM sales
WHERE product_id = 1
GROUP BY ROLLUP(state, product_id)
HAVING (GROUPING(state) = 0 and GROUPING(product_id) = 0) OR (GROUPING(state) = 1 and GROUPING(product_id) = 1)
ORDER BY state, product_id
limit 2 offset 0;

Change on - "limit 2 offset 2;"

I have dynamic sql builder and I don't understand how get total on second page when I have sorting. Is it possible doing in sql?

Solution

You can't do this directly with ROLLUP/LIMIT because the query will only output the rollup once. However, this query will do what you want:

SELECT state, product_id,
       SUM(quantity) as quantity
FROM sales
WHERE product_id = 1
GROUP BY state, product_id
ORDER BY state, product_id
limit 2 offset 0
UNION ALL
SELECT null, null,
       SUM(quantity) as quantity
FROM sales
WHERE product_id = 1