mirage mirage - 5 months ago 8
SQL Question

How do I get the difference from the rows in a column

so I have this query:

select sum( case when gender = 'Male'
then 1 else 0 end ) as male
from tbl_person


it will result to something like this:

+------+
| male |
+------+
| 2 |
| 5 |
| 10 |
+------+


What I want is to have a column beside it that will show the difference of each row, something like this:

+------+-----+
| male | gap |
+------+-----+
| 2 | |
| 5 | 3 |
| 10 | 5 |
+------+-----+


I tried some answers like here How to get difference between two rows for a column field?
But they don't seem to work. I think its because the column is a result of a Sum().

Answer

Use LAG window function

SELECT Male,
       COALESCE(Male - Lag(male)OVER(ORDER BY male), 0) AS gap
FROM   (SELECT Sum(CASE WHEN gender = 'Male' THEN 1 ELSE 0 END) AS male
        FROM   tbl_person) A