Beefy Boxes and Bandwidth Generously Provided by pair Networks
We don't bite newbies here... much
 
PerlMonks  

Re^8: Querying Saints in our Book (2) (updated)

by chacham (Prior)
on May 18, 2016 at 14:50 UTC ( [id://1163343]=note: print w/replies, xml ) Need Help??


in reply to Re^7: Querying Saints in our Book (2) (updated)
in thread Querying Saints in our Book (2)

Thank you. That is pretty neat. Let's see how it stacks up against the full data set. The link can be modified to show all the user's nodes.

WITH
    Data(rep)
AS
    (    
     SELECT    00 FROM Dual UNION ALL
     SELECT    00 FROM Dual UNION ALL
     SELECT    04 FROM Dual UNION ALL
     SELECT    04 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    07 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    16 FROM Dual UNION ALL
     SELECT    20 FROM Dual UNION ALL
     SELECT    04 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    05 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    09 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    23 FROM Dual UNION ALL
     SELECT    06 FROM Dual UNION ALL
     SELECT    13 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    19 FROM Dual UNION ALL
     SELECT    11 FROM Dual UNION ALL
     SELECT    09 FROM Dual UNION ALL
     SELECT    08 FROM Dual UNION ALL
     SELECT    07 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    00 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    04 FROM Dual UNION ALL
     SELECT    05 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    07 FROM Dual UNION ALL
     SELECT    07 FROM Dual UNION ALL
     SELECT    08 FROM Dual UNION ALL
     SELECT    20 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    06 FROM Dual UNION ALL
     SELECT    08 FROM Dual UNION ALL
     SELECT    04 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    14 FROM Dual UNION ALL
     SELECT    10 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    07 FROM Dual UNION ALL
     SELECT    15 FROM Dual UNION ALL
     SELECT    00 FROM Dual UNION ALL
     SELECT    04 FROM Dual UNION ALL
     SELECT    05 FROM Dual UNION ALL
     SELECT    10 FROM Dual UNION ALL
     SELECT    09 FROM Dual UNION ALL
     SELECT    05 FROM Dual UNION ALL
     SELECT    13 FROM Dual UNION ALL
     SELECT    08 FROM Dual UNION ALL
     SELECT    06 FROM Dual UNION ALL
     SELECT    13 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    07 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    06 FROM Dual UNION ALL
     SELECT    06 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    10 FROM Dual UNION ALL
     SELECT    05 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    08 FROM Dual UNION ALL
     SELECT    04 FROM Dual UNION ALL
     SELECT    13 FROM Dual UNION ALL
     SELECT    09 FROM Dual UNION ALL
     SELECT    04 FROM Dual UNION ALL
     SELECT    04 FROM Dual UNION ALL
     SELECT    09 FROM Dual UNION ALL
     SELECT    05 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    08 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    05 FROM Dual UNION ALL
     SELECT    14 FROM Dual UNION ALL
     SELECT    04 FROM Dual UNION ALL
     SELECT    10 FROM Dual UNION ALL
     SELECT    08 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    05 FROM Dual UNION ALL
     SELECT    28 FROM Dual UNION ALL
     SELECT    06 FROM Dual UNION ALL
     SELECT    07 FROM Dual UNION ALL
     SELECT    17 FROM Dual UNION ALL
     SELECT    10 FROM Dual UNION ALL
     SELECT    07 FROM Dual UNION ALL
     SELECT    05 FROM Dual UNION ALL
     SELECT    07 FROM Dual UNION ALL
     SELECT    08 FROM Dual UNION ALL
     SELECT    05 FROM Dual UNION ALL
     SELECT    08 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    04 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    06 FROM Dual UNION ALL
     SELECT    19 FROM Dual UNION ALL
     SELECT    07 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    19 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    04 FROM Dual UNION ALL
     SELECT    07 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    04 FROM Dual UNION ALL
     SELECT    05 FROM Dual UNION ALL
     SELECT    04 FROM Dual UNION ALL
     SELECT    05 FROM Dual UNION ALL
     SELECT    06 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    05 FROM Dual UNION ALL
     SELECT    07 FROM Dual UNION ALL
     SELECT    06 FROM Dual UNION ALL
     SELECT    11 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    07 FROM Dual UNION ALL
     SELECT    10 FROM Dual UNION ALL
     SELECT    05 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    09 FROM Dual UNION ALL
     SELECT    10 FROM Dual UNION ALL
     SELECT    11 FROM Dual UNION ALL
     SELECT    15 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    06 FROM Dual UNION ALL
     SELECT    10 FROM Dual UNION ALL
     SELECT    04 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    04 FROM Dual UNION ALL
     SELECT    04 FROM Dual UNION ALL
     SELECT    08 FROM Dual UNION ALL
     SELECT    08 FROM Dual UNION ALL
     SELECT    05 FROM Dual UNION ALL
     SELECT    04 FROM Dual UNION ALL
     SELECT    04 FROM Dual UNION ALL
     SELECT    07 FROM Dual UNION ALL
     SELECT    05 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    10 FROM Dual UNION ALL
     SELECT    04 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    04 FROM Dual UNION ALL
     SELECT    08 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    09 FROM Dual UNION ALL
     SELECT    09 FROM Dual UNION ALL
     SELECT    12 FROM Dual UNION ALL
     SELECT    10 FROM Dual UNION ALL
     SELECT    11 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    10 FROM Dual UNION ALL
     SELECT    07 FROM Dual UNION ALL
     SELECT    05 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    17 FROM Dual UNION ALL
     SELECT    04 FROM Dual UNION ALL
     SELECT    20 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    05 FROM Dual UNION ALL
     SELECT    04 FROM Dual UNION ALL
     SELECT    00 FROM Dual UNION ALL
     SELECT    05 FROM Dual UNION ALL
     SELECT    04 FROM Dual UNION ALL
     SELECT    13 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    04 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    04 FROM Dual UNION ALL
     SELECT    05 FROM Dual UNION ALL
     SELECT    07 FROM Dual UNION ALL
     SELECT    22 FROM Dual UNION ALL
     SELECT    05 FROM Dual UNION ALL
     SELECT    06 FROM Dual UNION ALL
     SELECT    18 FROM Dual UNION ALL
     SELECT    04 FROM Dual UNION ALL
     SELECT    08 FROM Dual UNION ALL
     SELECT    06 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    -2 FROM Dual UNION ALL
     SELECT    17 FROM Dual UNION ALL
     SELECT    29 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    00 FROM Dual UNION ALL
     SELECT    10 FROM Dual UNION ALL
     SELECT    05 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    05 FROM Dual UNION ALL
     SELECT    07 FROM Dual UNION ALL
     SELECT    04 FROM Dual UNION ALL
     SELECT    05 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    11 FROM Dual UNION ALL
     SELECT    04 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    -1 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    14 FROM Dual UNION ALL
     SELECT    04 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    05 FROM Dual UNION ALL
     SELECT    05 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    04 FROM Dual UNION ALL
     SELECT    04 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    -1 FROM Dual UNION ALL
     SELECT    04 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    09 FROM Dual UNION ALL
     SELECT    11 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    04 FROM Dual UNION ALL
     SELECT    09 FROM Dual UNION ALL
     SELECT    05 FROM Dual UNION ALL
     SELECT    05 FROM Dual UNION ALL
     SELECT    09 FROM Dual UNION ALL
     SELECT    14 FROM Dual UNION ALL
     SELECT    26 FROM Dual UNION ALL
     SELECT    20 FROM Dual UNION ALL
     SELECT    14 FROM Dual UNION ALL
     SELECT    15 FROM Dual UNION ALL
     SELECT    29 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    27 FROM Dual UNION ALL
     SELECT    -1 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    08 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    04 FROM Dual UNION ALL
     SELECT    00 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    10 FROM Dual UNION ALL
     SELECT    09 FROM Dual UNION ALL
     SELECT    12 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    00 FROM Dual UNION ALL
     SELECT    13 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    05 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    11 FROM Dual UNION ALL
     SELECT    00 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    04 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    -3 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    06 FROM Dual UNION ALL
     SELECT    10 FROM Dual UNION ALL
     SELECT    10 FROM Dual UNION ALL
     SELECT    16 FROM Dual UNION ALL
     SELECT    00 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    13 FROM Dual UNION ALL
     SELECT    09 FROM Dual UNION ALL
     SELECT    04 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    25 FROM Dual UNION ALL
     SELECT    08 FROM Dual UNION ALL
     SELECT    04 FROM Dual UNION ALL
     SELECT    16 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    -1 FROM Dual UNION ALL
     SELECT    04 FROM Dual UNION ALL
     SELECT    05 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    04 FROM Dual UNION ALL
     SELECT    00 FROM Dual UNION ALL
     SELECT    -2 FROM Dual UNION ALL
     SELECT    -7 FROM Dual UNION ALL
     SELECT    00 FROM Dual UNION ALL
     SELECT    07 FROM Dual UNION ALL
     SELECT    00 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    00 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    12 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    00 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    04 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    14 FROM Dual UNION ALL
     SELECT    00 FROM Dual UNION ALL
     SELECT    07 FROM Dual UNION ALL
     SELECT    00 FROM Dual UNION ALL
     SELECT    25 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    04 FROM Dual UNION ALL
     SELECT    11 FROM Dual UNION ALL
     SELECT    09 FROM Dual UNION ALL
     SELECT    08 FROM Dual UNION ALL
     SELECT    20 FROM Dual UNION ALL
     SELECT    17 FROM Dual UNION ALL
     SELECT    05 FROM Dual UNION ALL
     SELECT    17 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    11 FROM Dual UNION ALL
     SELECT    05 FROM Dual UNION ALL
     SELECT    04 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    06 FROM Dual UNION ALL
     SELECT    04 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    00 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    00 FROM Dual UNION ALL
     SELECT    04 FROM Dual UNION ALL
     SELECT    11 FROM Dual UNION ALL
     SELECT    12 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    00 FROM Dual UNION ALL
     SELECT    -4 FROM Dual UNION ALL
     SELECT    09 FROM Dual UNION ALL
     SELECT    00 FROM Dual UNION ALL
     SELECT    00 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    06 FROM Dual UNION ALL
     SELECT    00 FROM Dual UNION ALL
     SELECT    -4 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    00 FROM Dual UNION ALL
     SELECT    00 FROM Dual UNION ALL
     SELECT    12 FROM Dual UNION ALL
     SELECT    15 FROM Dual UNION ALL
     SELECT    04 FROM Dual UNION ALL
     SELECT    10 FROM Dual UNION ALL
     SELECT    04 FROM Dual UNION ALL
     SELECT    26 FROM Dual UNION ALL
     SELECT    04 FROM Dual UNION ALL
     SELECT    22 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    00 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    13 FROM Dual UNION ALL
     SELECT    18 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    04 FROM Dual UNION ALL
     SELECT    31 FROM Dual UNION ALL
     SELECT    09 FROM Dual UNION ALL
     SELECT    06 FROM Dual UNION ALL
     SELECT    14 FROM Dual UNION ALL
     SELECT    14 FROM Dual UNION ALL
     SELECT    09 FROM Dual UNION ALL
     SELECT    21 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    00 FROM Dual UNION ALL
     SELECT    -1 FROM Dual UNION ALL
     SELECT    07 FROM Dual UNION ALL
     SELECT    09 FROM Dual UNION ALL
     SELECT    12 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    04 FROM Dual UNION ALL
     SELECT    06 FROM Dual UNION ALL
     SELECT    05 FROM Dual UNION ALL
     SELECT    05 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    04 FROM Dual UNION ALL
     SELECT    13 FROM Dual UNION ALL
     SELECT    05 FROM Dual UNION ALL
     SELECT    04 FROM Dual UNION ALL
     SELECT    19 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    05 FROM Dual UNION ALL
     SELECT    05 FROM Dual UNION ALL
     SELECT    14 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    -2 FROM Dual UNION ALL
     SELECT    06 FROM Dual UNION ALL
     SELECT    07 FROM Dual UNION ALL
     SELECT    15 FROM Dual UNION ALL
     SELECT    07 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    07 FROM Dual UNION ALL
     SELECT    14 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    15 FROM Dual UNION ALL
     SELECT    07 FROM Dual UNION ALL
     SELECT    09 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    13 FROM Dual UNION ALL
     SELECT    11 FROM Dual UNION ALL
     SELECT    06 FROM Dual UNION ALL
     SELECT    05 FROM Dual UNION ALL
     SELECT    06 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    14 FROM Dual UNION ALL
     SELECT    08 FROM Dual UNION ALL
     SELECT    00 FROM Dual UNION ALL
     SELECT    -1 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    00 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    06 FROM Dual UNION ALL
     SELECT    00 FROM Dual UNION ALL
     SELECT    14 FROM Dual UNION ALL
     SELECT    -1 FROM Dual UNION ALL
     SELECT    00 FROM Dual UNION ALL
     SELECT    05 FROM Dual UNION ALL
     SELECT    13 FROM Dual UNION ALL
     SELECT    11 FROM Dual UNION ALL
     SELECT    19 FROM Dual UNION ALL
     SELECT    05 FROM Dual UNION ALL
     SELECT    18 FROM Dual UNION ALL
     SELECT    00 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    00 FROM Dual UNION ALL
     SELECT    00 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    05 FROM Dual UNION ALL
     SELECT    14 FROM Dual UNION ALL
     SELECT    08 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    00 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    13 FROM Dual UNION ALL
     SELECT    06 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    05 FROM Dual UNION ALL
     SELECT    24 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    -1 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    00 FROM Dual UNION ALL
     SELECT    -1 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    06 FROM Dual UNION ALL
     SELECT    04 FROM Dual UNION ALL
     SELECT    07 FROM Dual UNION ALL
     SELECT    04 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    05 FROM Dual UNION ALL
     SELECT    09 FROM Dual UNION ALL
     SELECT    00 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    -1 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    07 FROM Dual UNION ALL
     SELECT    07 FROM Dual UNION ALL
     SELECT    04 FROM Dual UNION ALL
     SELECT    18 FROM Dual UNION ALL
     SELECT    04 FROM Dual UNION ALL
     SELECT    12 FROM Dual UNION ALL
     SELECT    16 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    00 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    39 FROM Dual UNION ALL
     SELECT    07 FROM Dual UNION ALL
     SELECT    20 FROM Dual UNION ALL
     SELECT    08 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    07 FROM Dual UNION ALL
     SELECT    05 FROM Dual UNION ALL
     SELECT    04 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    04 FROM Dual UNION ALL
     SELECT    05 FROM Dual UNION ALL
     SELECT    15 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    07 FROM Dual UNION ALL
     SELECT    08 FROM Dual UNION ALL
     SELECT    07 FROM Dual UNION ALL
     SELECT    06 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    00 FROM Dual UNION ALL
     SELECT    16 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    00 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    -1 FROM Dual UNION ALL
     SELECT    00 FROM Dual UNION ALL
     SELECT    -4 FROM Dual UNION ALL
     SELECT    -3 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    05 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    07 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    05 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    -1 FROM Dual UNION ALL
     SELECT    00 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    06 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    07 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    09 FROM Dual UNION ALL
     SELECT    07 FROM Dual UNION ALL
     SELECT    18 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    00 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    05 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    04 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    00 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    00 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    05 FROM Dual UNION ALL
     SELECT    06 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    -1 FROM Dual UNION ALL
     SELECT    04 FROM Dual UNION ALL
     SELECT    07 FROM Dual UNION ALL
     SELECT    15 FROM Dual UNION ALL
     SELECT    -1 FROM Dual UNION ALL
     SELECT    00 FROM Dual UNION ALL
     SELECT    00 FROM Dual UNION ALL
     SELECT    -3 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    00 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    04 FROM Dual UNION ALL
     SELECT    -1 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    00 FROM Dual UNION ALL
     SELECT    -1 FROM Dual UNION ALL
     SELECT    00 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    00 FROM Dual UNION ALL
     SELECT    -1 FROM Dual UNION ALL
     SELECT    00 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    09 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    05 FROM Dual UNION ALL
     SELECT    04 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    00 FROM Dual UNION ALL
     SELECT    13 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    00 FROM Dual UNION ALL
     SELECT    07 FROM Dual UNION ALL
     SELECT    00 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    07 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    00 FROM Dual UNION ALL
     SELECT    00 FROM Dual UNION ALL
     SELECT    05 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    12 FROM Dual UNION ALL
     SELECT    04 FROM Dual UNION ALL
     SELECT    18 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    07 FROM Dual UNION ALL
     SELECT    06 FROM Dual UNION ALL
     SELECT    08 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    08 FROM Dual UNION ALL
     SELECT    06 FROM Dual UNION ALL
     SELECT    10 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    04 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    19 FROM Dual UNION ALL
     SELECT    -1 FROM Dual UNION ALL
     SELECT    08 FROM Dual UNION ALL
     SELECT    06 FROM Dual UNION ALL
     SELECT    13 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    09 FROM Dual UNION ALL
     SELECT    30 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    04 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    04 FROM Dual UNION ALL
     SELECT    05 FROM Dual UNION ALL
     SELECT    04 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    05 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    04 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    05 FROM Dual UNION ALL
     SELECT    13 FROM Dual UNION ALL
     SELECT    04 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    04 FROM Dual UNION ALL
     SELECT    07 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    00 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    09 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    00 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    05 FROM Dual UNION ALL
     SELECT    05 FROM Dual UNION ALL
     SELECT    00 FROM Dual UNION ALL
     SELECT    00 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    06 FROM Dual UNION ALL
     SELECT    04 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    04 FROM Dual UNION ALL
     SELECT    00 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    04 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    07 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    14 FROM Dual UNION ALL
     SELECT    08 FROM Dual UNION ALL
     SELECT    21 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    12 FROM Dual UNION ALL
     SELECT    07 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    06 FROM Dual UNION ALL
     SELECT    05 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    10 FROM Dual UNION ALL
     SELECT    10 FROM Dual UNION ALL
     SELECT    06 FROM Dual UNION ALL
     SELECT    04 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    00 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    04 FROM Dual UNION ALL
     SELECT    08 FROM Dual UNION ALL
     SELECT    00 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    07 FROM Dual UNION ALL
     SELECT    04 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    18 FROM Dual UNION ALL
     SELECT    04 FROM Dual UNION ALL
     SELECT    09 FROM Dual UNION ALL
     SELECT    20 FROM Dual UNION ALL
     SELECT    04 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    00 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    07 FROM Dual UNION ALL
     SELECT    05 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    06 FROM Dual UNION ALL
     SELECT    07 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    05 FROM Dual UNION ALL
     SELECT    06 FROM Dual UNION ALL
     SELECT    06 FROM Dual UNION ALL
     SELECT    11 FROM Dual UNION ALL
     SELECT    05 FROM Dual UNION ALL
     SELECT    22 FROM Dual UNION ALL
     SELECT    09 FROM Dual UNION ALL
     SELECT    17 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    11 FROM Dual UNION ALL
     SELECT    17 FROM Dual UNION ALL
     SELECT    04 FROM Dual UNION ALL
     SELECT    20 FROM Dual UNION ALL
     SELECT    14 FROM Dual UNION ALL
     SELECT    04 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    13 FROM Dual UNION ALL
     SELECT    10 FROM Dual UNION ALL
     SELECT    25 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    08 FROM Dual UNION ALL
     SELECT    10 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    16 FROM Dual UNION ALL
     SELECT    15 FROM Dual UNION ALL
     SELECT    26 FROM Dual UNION ALL
     SELECT    10 FROM Dual UNION ALL
     SELECT    13 FROM Dual UNION ALL
     SELECT    30 FROM Dual UNION ALL
     SELECT    12 FROM Dual UNION ALL
     SELECT    04 FROM Dual UNION ALL
     SELECT    06 FROM Dual UNION ALL
     SELECT    06 FROM Dual UNION ALL
     SELECT    09 FROM Dual UNION ALL
     SELECT    10 FROM Dual UNION ALL
     SELECT    20 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    00 FROM Dual UNION ALL
     SELECT    12 FROM Dual UNION ALL
     SELECT    00 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    13 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    21 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    10 FROM Dual UNION ALL
     SELECT    11 FROM Dual UNION ALL
     SELECT    00 FROM Dual UNION ALL
     SELECT    02 FROM Dual UNION ALL
     SELECT    04 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    13 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    00 FROM Dual UNION ALL
     SELECT    00 FROM Dual UNION ALL
     SELECT    00 FROM Dual UNION ALL
     SELECT    01 FROM Dual UNION ALL
     SELECT    06 FROM Dual UNION ALL
     SELECT    14 FROM Dual UNION ALL
     SELECT    13 FROM Dual UNION ALL
     SELECT    05 FROM Dual UNION ALL
     SELECT    33 FROM Dual UNION ALL
     SELECT    67 FROM Dual UNION ALL
     SELECT    03 FROM Dual UNION ALL
     SELECT    00 FROM Dual UNION ALL
     SELECT    06 FROM Dual UNION ALL
     SELECT    13 FROM Dual UNION ALL
     SELECT    00 FROM Dual UNION ALL
     SELECT    00 FROM Dual UNION ALL
     SELECT    26 FROM Dual UNION ALL
     SELECT    02 FROM Dual
    )
SELECT
    rep,
    COUNT(rep)                Amount,
    SUM(rep)                Total,
    SUM(CASE WHEN rep > 0 THEN rep END)    Total_X
FROM
    Data
GROUP BY
    ROLLUP(rep)
ORDER BY
    rep;

[download]
REP     AMOUNT      TOTAL    TOTAL_X
--- ---------- ---------- ----------
 -7          1         -7
 -4          3        -12
 -3          3         -9
 -2          3         -6
 -1         18        -18
  0         74          0
  1        124        124        124
  2        130        260        260
  3         94        282        282
  4         82        328        328
  5         61        305        305
  6         39        234        234
  7         44        308        308
  8         26        208        208
  9         27        243        243
 10         24        240        240
 11         15        165        165
 12         11        132        132
 13         21        273        273
 14         16        224        224
 15          9        135        135
 16          6         96         96
 17          7        119        119
 18          7        126        126
 19          6        114        114
 20          9        180        180
 21          3         63         63
 22          3         66         66
 23          1         23         23
 24          1         24         24
 25          3         75         75
 26          4        104        104
 27          1         27         27
 28          1         28         28
 29          2         58         58
 30          2         60         60
 31          1         31         31
 33          1         33         33
 39          1         39         39
 67          1         67         67
           885       4742       4794

[download]

So, i've got 885 nodes, and a total of 4794 rep, excluding the -52 from those with negative rep. Of course this doesn't show where a negative vote brought the rep back to zero (which means it gets the guaranteed xp inc at +1 more than once), but although its not terribly rare, at this level it shouldn't matter.

Does that match up with you you determined?

Replies are listed 'Best First'.
Re^9: Querying Saints in our Book (2) (posting bonus)
by tye (Sage) on May 18, 2016 at 15:00 UTC
    (which means it gets the guaranteed xp inc at +1 more than once)

    No.

    - tye        

[reply]

      Really? I ought to pay more attention then, because i am under the impression i've seen it every time it happened.

[reply]

In Section Perl Monks Discussion

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?

www.com | www.net | www.org
Node Status?
node history
Node Type: note [id://1163343]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? • Last hour • Other CB clients
Other Users?
Others taking refuge in the Monastery: (2)
As of 2024-04-25 03:48 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found