Postgresql – Combining two similar SQL queries

postgresql

I'm using the following SQL query twice with different values of limit. In my case, 40 and 100, though it doesn't really matter. My question is – can I combine these two queries into one query? It would probably be faster. sample is a PG array.

I don't know how much more information is needed, but if you need more details, please ask. I'm reluctant to add details about the schema unless necessary, since it will make the question much longer.

SELECT   SUM(motif)
FROM     (SELECT    ROW_NUMBER() OVER (PARTITION BY crossvalnum ORDER BY crossvalnum, pvalue DESC, margstat) AS r,
                    (seqindex IS NOT NULL)::INTEGER                                                        AS motif,
                    crossval.crossvalnum,
                    data.margstat,
                    data.pvalue
         FROM       data
                    INNER JOIN datasubgroup
                    ON         data.datasubgroup_id=datasubgroup.id
                    INNER JOIN crossval
                    ON         datasubgroup.crossval_id=crossval.id
         WHERE      data.seqindex                      =ANY(crossval.sample)
         OR         data.seqindex                  IS NULL
         )
         AS q
WHERE    q.r <= limit
GROUP BY crossvalnum;

The value for 40 is

sum 
-----
  25
  22
  19
  16
  24
(5 rows)

The value for 100 is

sum 
-----
  32
  28
  24
  23
  31
(5 rows)

Update: motif is defined an an indicator variable, (seqindex IS NOT NULL)::INTEGER. The SUM corresponds with the GROUP BY crossvalnum as an aggregate query. So the sum happens across each crossvalnum group. So there is a window function inside an aggregate query. I want the output to be two columns.

Best Answer

Will it work for you ?

SELECT SUM(CASE WHEN r <=LEAST(40,100) THEN motif ELSE 0 END) as sum1,
SUM(motif) as sum2
FROM (...) q  -- or AS q , whatever version you prefer
WHERE    q.r <= GREATEST(40,100)
GROUP BY crossvalnum;
Related Question