SEO

Sample data:
colA,colB,sum
33996344,33996351,8
50331648,67276831,16945184
50331648,68257567,17925920
67276832,67276847,16
67276848,67277023,176
67277024,67277031,8

Wanted output:
33996344,33996351,8
50331648,68257567,17925920

Sum is done as follows (colB-colA+1).
Any idea how to get this outcome ?

This will give incorrect unique count, because it simply sums all up.
SELECT sum(colB-colA+1) FROM tbl

On Tue, 11 Mar 2008 11:20:06 +0100, Kim <kimslot@gmail.com> wrote:

> Sample data:
> colA,colB,sum
> 33996344,33996351,8
> 50331648,67276831,16945184
> 50331648,68257567,17925920
> 67276832,67276847,16
> 67276848,67277023,176
> 67277024,67277031,8
>
> Wanted output:
> 33996344,33996351,8
> 50331648,68257567,17925920
>
> Sum is done as follows (colB-colA+1).
> Any idea how to get this outcome ?
>
> This will give incorrect unique count, because it simply sums all up.
> SELECT sum(colB-colA+1) FROM tbl

If this is not what you want:
SELECT sum(colB-colA+1) FROM tbl
GROUP BY colA,colB

.... you'll have to explain the logic further, as I can't really see it.
What overlaps what, how, an how should that be handled?
--
Rik Wasmus

Kim schreef:
> Sample data:
> colA,colB,sum
> 33996344,33996351,8
> 50331648,67276831,16945184
> 50331648,68257567,17925920
> 67276832,67276847,16
> 67276848,67277023,176
> 67277024,67277031,8
>
> Wanted output:
> 33996344,33996351,8
> 50331648,68257567,17925920
>
> Sum is done as follows (colB-colA+1).
> Any idea how to get this outcome ?
>
> This will give incorrect unique count, because it simply sums all up.
> SELECT sum(colB-colA+1) FROM tbl

select colA, max(colB) colB , (max(colB)-ColA+1) as sum
from tbl group by colA;


--
Luuk

On Mar 11, 11:25 am, "Rik Wasmus" <luiheidsgoe...@hotmail.com> wrote:
> On Tue, 11 Mar 2008 11:20:06 +0100, Kim <kims...@gmail.com> wrote:
> > Sample data:
> > colA,colB,sum
> > 33996344,33996351,8
> > 50331648,67276831,16945184
> > 50331648,68257567,17925920
> > 67276832,67276847,16
> > 67276848,67277023,176
> > 67277024,67277031,8
>
> > Wanted output:
> > 33996344,33996351,8
> > 50331648,68257567,17925920
>
> > Sum is done as follows (colB-colA+1).
> > Any idea how to get this outcome ?
>
> > This will give incorrect unique count, because it simply sums all up.
> > SELECT sum(colB-colA+1) FROM tbl
>
> If this is not what you want:
> SELECT sum(colB-colA+1) FROM tbl
> GROUP BY colA,colB
>
> ... you'll have to explain the logic further, as I can't really see it.
> What overlaps what, how, an how should that be handled?
> --
> Rik Wasmus

To Rik Wasmus:
Its not what I want.
If you look at the sample when you should see 6 rows. Of these only
the 2 listed in "wanted output" is unique.

Here is why:
Row 1 is unique already, so no filtering needed.
Row 2 is not unique because row 3 contains the same + more and
therefor needs to be filtered out.
Row 3 is unique because it has the largest range with no overlapping.
Row 4-6 are all in the range which row 3 has and therefor needs to be
filtered out.


To Luuk:
Thanks. Its step in the right direction as it can filter out rows with
the same colA value but different colB value. Result:
33996344,33996351,8
50331648,68257567,17925920
67276832,67276847,16
67276848,67277023,176
67277024,67277031,8


To all:
How I get the wanted outcome has no restrictions as long as it can be
done using one or more queries. If all fails, then I must resort to
looping thru all rows in tbl, which I would prefer not to do because
there is millions of rows.

On Tue, 11 Mar 2008 13:57:37 +0100, Kim <kimslot@gmail.com> wrote:

> On Mar 11, 11:25 am, "Rik Wasmus" <luiheidsgoe...@hotmail.com> wrote:
>> On Tue, 11 Mar 2008 11:20:06 +0100, Kim <kims...@gmail.com> wrote:
>> > Sample data:
>> > colA,colB,sum
>> > 33996344,33996351,8
>> > 50331648,67276831,16945184
>> > 50331648,68257567,17925920
>> > 67276832,67276847,16
>> > 67276848,67277023,176
>> > 67277024,67277031,8
>>
>> > Wanted output:
>> > 33996344,33996351,8
>> > 50331648,68257567,17925920
>>
>> > Sum is done as follows (colB-colA+1).
>> > Any idea how to get this outcome ?
>>
>> > This will give incorrect unique count, because it simply sums all up.
>> > SELECT sum(colB-colA+1) FROM tbl
>>
>> If this is not what you want:
>> SELECT sum(colB-colA+1) FROM tbl
>> GROUP BY colA,colB
>>
>> ... you'll have to explain the logic further, as I can't really see it.
>> What overlaps what, how, an how should that be handled?
>
> To Rik Wasmus:
> Its not what I want.
> If you look at the sample when you should see 6 rows. Of these only
> the 2 listed in "wanted output" is unique.
>
> Here is why:
> Row 1 is unique already, so no filtering needed.
> Row 2 is not unique because row 3 contains the same + more and
> therefor needs to be filtered out.
> Row 3 is unique because it has the largest range with no overlapping.
> Row 4-6 are all in the range which row 3 has and therefor needs to be
> filtered out.

So, if I understand correctly, you want the 'largest' range, and skip all
records that fall in a bigger range?

If that's the case, what should happen with:
1 4
2 6
1 3
8 12
9 10

The problem here is row 2 falls partly in row 1, but I assume:
1 4
2 6
8 12

If I assume these should count as different rows, I'd say this one:
SELECT
a.colA, a.colB, (a.colB-a.colA+1)
FROM tablename a
LEFT JOIN tablename b
ON b.colA <= a.colA
AND b.colA >= a.colA
# having a pk would help
# now we have to make sure it
# doesn't match on it's own row:
AND NOT (a.colA = b.colA AND a.colB = b.colB)
WHERE b.colA IS NULL
--
Rik Wasmus

Kim schreef:
> On Mar 11, 11:25 am, "Rik Wasmus" <luiheidsgoe...@hotmail.com> wrote:
>> On Tue, 11 Mar 2008 11:20:06 +0100, Kim <kims...@gmail.com> wrote:
>>> Sample data:
>>> colA,colB,sum
>>> 33996344,33996351,8
>>> 50331648,67276831,16945184
>>> 50331648,68257567,17925920
>>> 67276832,67276847,16
>>> 67276848,67277023,176
>>> 67277024,67277031,8
>>> Wanted output:
>>> 33996344,33996351,8
>>> 50331648,68257567,17925920
>>> Sum is done as follows (colB-colA+1).
>>> Any idea how to get this outcome ?
>>> This will give incorrect unique count, because it simply sums all up.
>>> SELECT sum(colB-colA+1) FROM tbl
>> If this is not what you want:
>> SELECT sum(colB-colA+1) FROM tbl
>> GROUP BY colA,colB
>>
>> ... you'll have to explain the logic further, as I can't really see it.
>> What overlaps what, how, an how should that be handled?
>> --
>> Rik Wasmus
>
> To Rik Wasmus:
> Its not what I want.
> If you look at the sample when you should see 6 rows. Of these only
> the 2 listed in "wanted output" is unique.
>
> Here is why:
> Row 1 is unique already, so no filtering needed.
> Row 2 is not unique because row 3 contains the same + more and
> therefor needs to be filtered out.
> Row 3 is unique because it has the largest range with no overlapping.
> Row 4-6 are all in the range which row 3 has and therefor needs to be
> filtered out.

that was totaly no obvious from you original post ;-)

>
>
> To Luuk:
> Thanks. Its step in the right direction as it can filter out rows with
> the same colA value but different colB value. Result:
> 33996344,33996351,8
> 50331648,68257567,17925920
> 67276832,67276847,16
> 67276848,67277023,176
> 67277024,67277031,8
>
>
> To all:
> How I get the wanted outcome has no restrictions as long as it can be
> done using one or more queries. If all fails, then I must resort to
> looping thru all rows in tbl, which I would prefer not to do because
> there is millions of rows.

select colA, max(colB) , (max(colB)-ColA+1) as sum
from tbl T1
where colA>ifnull((select max(colB) from tbl where colA<T1.colA),0)
group by colA;


--
Luuk

On Mar 11, 2:11 pm, "Rik Wasmus" <luiheidsgoe...@hotmail.com> wrote:
> On Tue, 11 Mar 2008 13:57:37 +0100, Kim <kims...@gmail.com> wrote:
> > On Mar 11, 11:25 am, "Rik Wasmus" <luiheidsgoe...@hotmail.com> wrote:
> >> On Tue, 11 Mar 2008 11:20:06 +0100, Kim <kims...@gmail.com> wrote:
> >> > Sample data:
> >> > colA,colB,sum
> >> > 33996344,33996351,8
> >> > 50331648,67276831,16945184
> >> > 50331648,68257567,17925920
> >> > 67276832,67276847,16
> >> > 67276848,67277023,176
> >> > 67277024,67277031,8
>
> >> > Wanted output:
> >> > 33996344,33996351,8
> >> > 50331648,68257567,17925920
>
> >> > Sum is done as follows (colB-colA+1).
> >> > Any idea how to get this outcome ?
>
> >> > This will give incorrect unique count, because it simply sums all up.
> >> > SELECT sum(colB-colA+1) FROM tbl
>
> >> If this is not what you want:
> >> SELECT sum(colB-colA+1) FROM tbl
> >> GROUP BY colA,colB
>
> >> ... you'll have to explain the logic further, as I can't really see it.
> >> What overlaps what, how, an how should that be handled?
>
> > To Rik Wasmus:
> > Its not what I want.
> > If you look at the sample when you should see 6 rows. Of these only
> > the 2 listed in "wanted output" is unique.
>
> > Here is why:
> > Row 1 is unique already, so no filtering needed.
> > Row 2 is not unique because row 3 contains the same + more and
> > therefor needs to be filtered out.
> > Row 3 is unique because it has the largest range with no overlapping.
> > Row 4-6 are all in the range which row 3 has and therefor needs to be
> > filtered out.
>
> So, if I understand correctly, you want the 'largest' range, and skip all
> records that fall in a bigger range?
>
> If that's the case, what should happen with:
> 1 4
> 2 6
> 1 3
> 8 12
> 9 10
>
> The problem here is row 2 falls partly in row 1, but I assume:
> 1 4
> 2 6
> 8 12
>
> If I assume these should count as different rows, I'd say this one:
> SELECT
> a.colA, a.colB, (a.colB-a.colA+1)
> FROM tablename a
> LEFT JOIN tablename b
> ON b.colA <= a.colA
> AND b.colA >= a.colA
> # having a pk would help
> # now we have to make sure it
> # doesn't match on it's own row:
> AND NOT (a.colA = b.colA AND a.colB = b.colB)
> WHERE b.colA IS NULL
> --
> Rik Wasmus

Your understanding is correct, as is your assumption.
I had not yet considered the way you outline of a range overlapping
another range. I now see dark future of doing this without looping...

Regarding your query on sample data, this is what I get:
33996344,33996351,8
67276832,67276847,16
67276848,67277023,176
67277024,67277031,8

The table does not have PK, since tbl is sorted upon creation, which
happens once a month.
As I see it only a stored procedure utilizing a temporary table (TT)
will work. The TT will only have 1 field which contains a single
number.
Inserts will happen once a check has confirmed that the number is not
already in TT. After looping, a simply count(*) on TT will give the
desired result.

Other suggestions are welcome.

On Tue, 11 Mar 2008 14:55:10 +0100, Kim <kimslot@gmail.com> wrote:

> On Mar 11, 2:11 pm, "Rik Wasmus" <luiheidsgoe...@hotmail.com> wrote:
>> On Tue, 11 Mar 2008 13:57:37 +0100, Kim <kims...@gmail.com> wrote:
>> > On Mar 11, 11:25 am, "Rik Wasmus" <luiheidsgoe...@hotmail.com> wrote:
>> >> On Tue, 11 Mar 2008 11:20:06 +0100, Kim <kims...@gmail.com> wrote:
>> >> > Sample data:
>> >> > colA,colB,sum
>> >> > 33996344,33996351,8
>> >> > 50331648,67276831,16945184
>> >> > 50331648,68257567,17925920
>> >> > 67276832,67276847,16
>> >> > 67276848,67277023,176
>> >> > 67277024,67277031,8
>>
>> >> > Wanted output:
>> >> > 33996344,33996351,8
>> >> > 50331648,68257567,17925920
>>
>> >> > Sum is done as follows (colB-colA+1).
>> >> > Any idea how to get this outcome ?
>>
>> >> > This will give incorrect unique count, because it simply sums all
>> up.
>> >> > SELECT sum(colB-colA+1) FROM tbl
>>
>> >> If this is not what you want:
>> >> SELECT sum(colB-colA+1) FROM tbl
>> >> GROUP BY colA,colB
>>
>> >> ... you'll have to explain the logic further, as I can't really see
>> it.
>> >> What overlaps what, how, an how should that be handled?
>>
>> > To Rik Wasmus:
>> > Its not what I want.
>> > If you look at the sample when you should see 6 rows. Of these only
>> > the 2 listed in "wanted output" is unique.
>>
>> > Here is why:
>> > Row 1 is unique already, so no filtering needed.
>> > Row 2 is not unique because row 3 contains the same + more and
>> > therefor needs to be filtered out.
>> > Row 3 is unique because it has the largest range with no overlapping.
>> > Row 4-6 are all in the range which row 3 has and therefor needs to be
>> > filtered out.
>>
>> So, if I understand correctly, you want the 'largest' range, and skip
>> all
>> records that fall in a bigger range?
>>
>> If that's the case, what should happen with:
>> 1 4
>> 2 6
>> 1 3
>> 8 12
>> 9 10
>>
>> The problem here is row 2 falls partly in row 1, but I assume:
>> 1 4
>> 2 6
>> 8 12
>>
>> If I assume these should count as different rows, I'd say this one:
>> SELECT
>> a.colA, a.colB, (a.colB-a.colA+1)
>> FROM tablename a
>> LEFT JOIN tablename b
>> ON b.colA <= a.colA
>> AND b.colA >= a.colA
>> # having a pk would help
>> # now we have to make sure it
>> # doesn't match on it's own row:
>> AND NOT (a.colA = b.colA AND a.colB = b.colB)
>> WHERE b.colA IS NULL
>
> Your understanding is correct, as is your assumption.
>
> I had not yet considered the way you outline of a range overlapping
> another range. I now see dark future of doing this without looping...
>
> Regarding your query on sample data, this is what I get:
> 33996344,33996351,8
> 67276832,67276847,16
> 67276848,67277023,176
> 67277024,67277031,8

Hmmz, just an error in typing it up, the second JOIN clause should
offcourse be on colB:
mysql> SELECT
-> a.colA, a.colB, (a.colB-a.colA+1)
-> FROM tester a
-> LEFT JOIN tester b
-> ON b.colA <= a.colA
-> AND b.colB >= a.colB
-> # having a pk would help
-> # now we have to make sure it
-> # doesn't match on it's own row:
-> AND NOT (a.colA = b.colA AND a.colB = b.colB)
-> WHERE b.colA IS NULL;
+----------+----------+-------------------+
| colA | colB | (a.colB-a.colA+1) |
+----------+----------+-------------------+
| 33996344 | 33996351 | 8 |
| 50331648 | 68257567 | 17925920 |
+----------+----------+-------------------+
2 rows in set (0.00 sec)
--
Rik Wasmus

On Mar 11, 3:17 pm, "Rik Wasmus" <luiheidsgoe...@hotmail.com> wrote:
> On Tue, 11 Mar 2008 14:55:10 +0100, Kim <kims...@gmail.com> wrote:
> > On Mar 11, 2:11 pm, "Rik Wasmus" <luiheidsgoe...@hotmail.com> wrote:
> >> On Tue, 11 Mar 2008 13:57:37 +0100, Kim <kims...@gmail.com> wrote:
> >> > On Mar 11, 11:25 am, "Rik Wasmus" <luiheidsgoe...@hotmail.com> wrote:
> >> >> On Tue, 11 Mar 2008 11:20:06 +0100, Kim <kims...@gmail.com> wrote:
> >> >> > Sample data:
> >> >> > colA,colB,sum
> >> >> > 33996344,33996351,8
> >> >> > 50331648,67276831,16945184
> >> >> > 50331648,68257567,17925920
> >> >> > 67276832,67276847,16
> >> >> > 67276848,67277023,176
> >> >> > 67277024,67277031,8
>
> >> >> > Wanted output:
> >> >> > 33996344,33996351,8
> >> >> > 50331648,68257567,17925920
>
> >> >> > Sum is done as follows (colB-colA+1).
> >> >> > Any idea how to get this outcome ?
>
> >> >> > This will give incorrect unique count, because it simply sums all
> >> up.
> >> >> > SELECT sum(colB-colA+1) FROM tbl
>
> >> >> If this is not what you want:
> >> >> SELECT sum(colB-colA+1) FROM tbl
> >> >> GROUP BY colA,colB
>
> >> >> ... you'll have to explain the logic further, as I can't really see
> >> it.
> >> >> What overlaps what, how, an how should that be handled?
>
> >> > To Rik Wasmus:
> >> > Its not what I want.
> >> > If you look at the sample when you should see 6 rows. Of these only
> >> > the 2 listed in "wanted output" is unique.
>
> >> > Here is why:
> >> > Row 1 is unique already, so no filtering needed.
> >> > Row 2 is not unique because row 3 contains the same + more and
> >> > therefor needs to be filtered out.
> >> > Row 3 is unique because it has the largest range with no overlapping.
> >> > Row 4-6 are all in the range which row 3 has and therefor needs to be
> >> > filtered out.
>
> >> So, if I understand correctly, you want the 'largest' range, and skip
> >> all
> >> records that fall in a bigger range?
>
> >> If that's the case, what should happen with:
> >> 1 4
> >> 2 6
> >> 1 3
> >> 8 12
> >> 9 10
>
> >> The problem here is row 2 falls partly in row 1, but I assume:
> >> 1 4
> >> 2 6
> >> 8 12
>
> >> If I assume these should count as different rows, I'd say this one:
> >> SELECT
> >> a.colA, a.colB, (a.colB-a.colA+1)
> >> FROM tablename a
> >> LEFT JOIN tablename b
> >> ON b.colA <= a.colA
> >> AND b.colA >= a.colA
> >> # having a pk would help
> >> # now we have to make sure it
> >> # doesn't match on it's own row:
> >> AND NOT (a.colA = b.colA AND a.colB = b.colB)
> >> WHERE b.colA IS NULL
>
> > Your understanding is correct, as is your assumption.
>
> > I had not yet considered the way you outline of a range overlapping
> > another range. I now see dark future of doing this without looping...
>
> > Regarding your query on sample data, this is what I get:
> > 33996344,33996351,8
> > 67276832,67276847,16
> > 67276848,67277023,176
> > 67277024,67277031,8
>
> Hmmz, just an error in typing it up, the second JOIN clause should
> offcourse be on colB:
> mysql> SELECT
> -> a.colA, a.colB, (a.colB-a.colA+1)
> -> FROM tester a
> -> LEFT JOIN tester b
> -> ON b.colA <= a.colA
> -> AND b.colB >= a.colB
> -> # having a pk would help
> -> # now we have to make sure it
> -> # doesn't match on it's own row:
> -> AND NOT (a.colA = b.colA AND a.colB = b.colB)
> -> WHERE b.colA IS NULL;
> +----------+----------+-------------------+
> | colA | colB | (a.colB-a.colA+1) |
> +----------+----------+-------------------+
> | 33996344 | 33996351 | 8 |
> | 50331648 | 68257567 | 17925920 |
> +----------+----------+-------------------+
> 2 rows in set (0.00 sec)
> --
> Rik Wasmus

Yours actually work faster than Luuk's.
However the time it takes to sum up unique values on 10.000 rows takes
94sec, which isnt fast in any way. I dare not think of how much time
it will take for millions of rows.
SELECT
sum(a.colB-a.colA+1)
FROM tbl a
LEFT JOIN tbl b
ON b.colA <= a.colA
AND b.colB >= a.colB
AND NOT (a.colA = b.colA AND a.colB = b.colB)
WHERE b.colA IS NULL

Why is the where clause there ? If its avoid NULL values, then its not
a problem since there are none in tbl.

Therefor am I trying the SP way I described in my last post.

On Wed, 12 Mar 2008 10:57:54 +0100, Kim <kimslot@gmail.com> wrote:

> On Mar 11, 3:17 pm, "Rik Wasmus" <luiheidsgoe...@hotmail.com> wrote:
>> On Tue, 11 Mar 2008 14:55:10 +0100, Kim <kims...@gmail.com> wrote:
>> > On Mar 11, 2:11 pm, "Rik Wasmus" <luiheidsgoe...@hotmail.com> wrote:
>> >> On Tue, 11 Mar 2008 13:57:37 +0100, Kim <kims...@gmail.com> wrote:
>> >> > On Mar 11, 11:25 am, "Rik Wasmus" <luiheidsgoe...@hotmail.com>
>> wrote:
>> >> >> On Tue, 11 Mar 2008 11:20:06 +0100, Kim <kims...@gmail.com> wrote:
>> >> >> > Sample data:
>> >> >> > colA,colB,sum
>> >> >> > 33996344,33996351,8
>> >> >> > 50331648,67276831,16945184
>> >> >> > 50331648,68257567,17925920
>> >> >> > 67276832,67276847,16
>> >> >> > 67276848,67277023,176
>> >> >> > 67277024,67277031,8
>>
>> >> >> > Wanted output:
>> >> >> > 33996344,33996351,8
>> >> >> > 50331648,68257567,17925920
>>
>> >> >> > Sum is done as follows (colB-colA+1).
>> >> >> > Any idea how to get this outcome ?
>>
>> >> >> > This will give incorrect unique count, because it simply sums
>> all
>> >> up.
>> >> >> > SELECT sum(colB-colA+1) FROM tbl
>>
>> >> >> If this is not what you want:
>> >> >> SELECT sum(colB-colA+1) FROM tbl
>> >> >> GROUP BY colA,colB
>>
>> >> >> ... you'll have to explain the logic further, as I can't really
>> see
>> >> it.
>> >> >> What overlaps what, how, an how should that be handled?
>>
>> >> > To Rik Wasmus:
>> >> > Its not what I want.
>> >> > If you look at the sample when you should see 6 rows. Of these only
>> >> > the 2 listed in "wanted output" is unique.
>>
>> >> > Here is why:
>> >> > Row 1 is unique already, so no filtering needed.
>> >> > Row 2 is not unique because row 3 contains the same + more and
>> >> > therefor needs to be filtered out.
>> >> > Row 3 is unique because it has the largest range with no
>> overlapping.
>> >> > Row 4-6 are all in the range which row 3 has and therefor needs to
>> be
>> >> > filtered out.
>>
>> >> So, if I understand correctly, you want the 'largest' range, and skip
>> >> all
>> >> records that fall in a bigger range?
>>
>> >> If that's the case, what should happen with:
>> >> 1 4
>> >> 2 6
>> >> 1 3
>> >> 8 12
>> >> 9 10
>>
>> >> The problem here is row 2 falls partly in row 1, but I assume:
>> >> 1 4
>> >> 2 6
>> >> 8 12
>>
>> >> If I assume these should count as different rows, I'd say this one:
>> >> SELECT
>> >> a.colA, a.colB, (a.colB-a.colA+1)
>> >> FROM tablename a
>> >> LEFT JOIN tablename b
>> >> ON b.colA <= a.colA
>> >> AND b.colA >= a.colA
>> >> # having a pk would help
>> >> # now we have to make sure it
>> >> # doesn't match on it's own row:
>> >> AND NOT (a.colA = b.colA AND a.colB = b.colB)
>> >> WHERE b.colA IS NULL
>>
>> > Your understanding is correct, as is your assumption.
>>
>> > I had not yet considered the way you outline of a range overlapping
>> > another range. I now see dark future of doing this without looping....
>>
>> > Regarding your query on sample data, this is what I get:
>> > 33996344,33996351,8
>> > 67276832,67276847,16
>> > 67276848,67277023,176
>> > 67277024,67277031,8
>>
>> Hmmz, just an error in typing it up, the second JOIN clause should
>> offcourse be on colB:
>> mysql> SELECT
>> -> a.colA, a.colB, (a.colB-a.colA+1)
>> -> FROM tester a
>> -> LEFT JOIN tester b
>> -> ON b.colA <= a.colA
>> -> AND b.colB >= a.colB
>> -> # having a pk would help
>> -> # now we have to make sure it
>> -> # doesn't match on it's own row:
>> -> AND NOT (a.colA = b.colA AND a.colB = b.colB)
>> -> WHERE b.colA IS NULL;
>> +----------+----------+-------------------+
>> | colA | colB | (a.colB-a.colA+1) |
>> +----------+----------+-------------------+
>> | 33996344 | 33996351 | 8 |
>> | 50331648 | 68257567 | 17925920 |
>> +----------+----------+-------------------+
>> 2 rows in set (0.00 sec)
>
> Yours actually work faster than Luuk's.
> However the time it takes to sum up unique values on 10.000 rows takes
> 94sec, which isnt fast in any way. I dare not think of how much time
> it will take for millions of rows.

Could you run it with an EXPLAIN before the SELECT and give us the output?

> SELECT
> sum(a.colB-a.colA+1)
> FROM tbl a
> LEFT JOIN tbl b
> ON b.colA <= a.colA
> AND b.colB >= a.colB
> AND NOT (a.colA = b.colA AND a.colB = b.colB)
> WHERE b.colA IS NULL
>
> Why is the where clause there ? If its avoid NULL values, then its not
> a problem since there are none in tbl.

The WHERE clause is exactly there and works because colA is never NULL
Translated into human readable language this query is something like:

Give the result of sum(a.colB-a.colA+1) from tbl, where there are no rows
(colA IS NULL) in tbl where colA-colB falls into the range of this row
(the join clause).

> Therefor am I trying the SP way I described in my last post.

One query with the right indexes (I sure hope you have some...) is often
way faster then an SP.
--
Rik Wasmus