asked on

SQL Query to find running count of rows

I have a table (ex: INCIDENTS) that has columns (ID, DESC etc.,). The table has 2mil rows with 500K distinct ID's. I want to know the number of rows that fall in an ID range of 0-2mil, 2mil-4mil, 4mil-6mil........every 2mil increments until the range reaches the max(ID). The desired output is as below and I cannot create any temp tables.

Any suggestions on how this can be achieved?

Id-Range count(rows)
0-2million 250k
2m-4m 500k
4m-6m 500k
null 750k

David Kroll

select '0-2 Million' as range, count(id) as rows
from incidents
where id between 0 and 1999999
union
select '2m-4m' as range, count(id) as rows
from incidents
where id between 2000000 and 3999999
union
select '4m-6m' as range, count(id) as rows
from incidents
where id between 4000000 and 6000000
union
select 'NULL' as range, count(id) as rows
from incidents
where id not between 0 and 6000000

Sean Stuber

SELECT 2000 * idrange || '-' || 2000 * (idrange + 1) idrange,
SUM(cnt) OVER (ORDER BY idrange) totalcount
FROM (SELECT FLOOR(id / 2000) idrange, COUNT(*) cnt
FROM incidents
GROUP BY FLOOR(id / 2000))
ORDER BY idrange

MRPT

ASKER

Thank you for the response.
This way, I will have to first find the max(ID) and then write multiple union statements accordingly. I want the query itself to find the max(ID) and determine how many ranges should it display (by specifying a pre-defined interval of 2mil).

Can that be done?

SOLUTION

Sean Stuber

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

MRPT

ASKER

I tried the above query and below is the result I got. It looks like the rows are being counted multiple times which shouldn't happen.
The actual number of rows that the table has is "18,988,216". But, if I sum the TOTALCOUNT column below, I get 208,328,691

IDRANGE TOTALCOUNT
0-2000000        999832
2000000-4000000        1997076
4000000-6000000        2986602
6000000-8000000        3981952
8000000-10000000        4978488
10000000-12000000      5972505
12000000-14000000      6970673
14000000-16000000      7969259
16000000-18000000      8966878
18000000-20000000      9964623
20000000-22000000      10962715
22000000-24000000      11960538
24000000-26000000      12956697
26000000-28000000      13952989
28000000-30000000      14951525
30000000-32000000      15948494
32000000-34000000      16946037
34000000-36000000      17944632
36000000-38000000      18928960
38000000-40000000      18988216
       Sum 208328691

SOLUTION

PortletPaul

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

MRPT

ASKER

Thank you Paul and sdstuber.
I still am facing an issue.
The max(id) is 32,811,198. But the above query is giving IDRANGE until 38000000-39999999? The max range that should show up is just until 32000000-33999999

ASKER CERTIFIED SOLUTION

PortletPaul

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

MRPT

ASKER

Thanks a lot.

Sean Stuber

why the penalty grade of B?