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

Question

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().

Your current query should result only one row. How you got three rows in result did you forgot to add `group by`? — Pரதீப், Jun 27 '16 at 06:04

Pரதீப் · Accepted Answer · 2016-06-27T06:01:10.347

1

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

edited Jun 27 '16 at 06:01

answered Jun 27 '16 at 05:52

Pரதீப்

91,748
19
131
172

Male is not a Column, Gender is a column. So have u forgot using Case Statement to filter out? – Ruhaan Jun 27 '16 at 05:55
I tried that but it only produce an error: column "male" does not exist. – mirage Jun 27 '16 at 05:57

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

1 Answers1