Question 191759 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Get the sum of a column for exactly n rows

I have a table like this:

CREATE TABLE test(
    id INT AUTO_INCREMENT PRIMARY KEY,
    server_id VARCHAR(15) NOT NULL,
    secret_number INT NOT NULL,
    processing_status VARCHAR(15) DEFAULT 'UNPROCESSED',
    ts TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3)
);

I want to get the SUM(secret_number) of EXACTLY top 5 records ORDER BY id which are in UNPROCESSED status. The below query works EXCEPT that it gives me the sum even when there are less than 5 matching records.

SELECT SUM(secret_number)
FROM (
    SELECT secret_number
    FROM test
    WHERE processing_status = 'UNPROCESSED' AND server_id = 'R_SERVER'
    ORDER BY id LIMIT 5
) t1;

How can I modify this (or write a new query) that either returns NULL when there are less than 5 records matching the criteria? Or, if it can return me the COUNT along with SUM then in my Python code, I can check if returned count is less than 5, then ignore the results.

Tags:

3

Answers


  1. 0

    if it can return me the COUNT along with SUM

    You can do this using row_number() :

    SELECT COUNT(*), SUM(secret_number)
FROM (
  SELECT *, ROW_NUMBER() OVER (ORDER BY id) as rn
  FROM test
  WHERE processing_status = 'UNPROCESSED'
) AS s
WHERE rn <= 5
    Login or Signup to reply.
  2. 0

    Here is one way to do it with window functions and aggregation:

    select sum(secret_number end) as res
from (
    select t.*, row_number() over(order by id) rn
    from test t
    where processing_status = 'UNPROCESSED'
) t
where rn <= 5
having count(*) = 5

    The subquery enumerates the record by id; then the outer query ensures that the subquery returned at least 5 records (if not, an empty resultset is returned), and takes the sum of the top 5.

    If you do want a null value instead of an empty resultset when there are less than 5 records, then:

    select case when count(*) >= 5 then sum(case when rn <= 5 then secret_number end) end as res
from (
    select t.*, row_number() over(order by id) rn
    from test t
    where processing_status = 'UNPROCESSED'
) t
    Login or Signup to reply.
  3. 0

    Two solutions based on top of your original query without over complicating stuff:

    Use CASE:

    SELECT CASE WHEN COUNT(*) = 5 THEN SUM(secret_number) END
-- one row containing sum or null

    Use HAVING:

    HAVING COUNT(*) = 5
-- one row with sum
-- or zero rows
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search