Count error?

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Count error?

Jose Isaias Cabrera-4

Greetings.

Please take a look at the following:


create table t0 (n INTEGER PRIMARY KEY, a, b, c, d, e, idate);

insert into t0 (a, b, c, d, e, idate) values ('p001', 'a', 1, 'n', 4, '2019-02-11');

insert into t0 (a, b, c, d, e, idate) values ('p002', 'a', 1, 'n', 4, '2019-02-11');

insert into t0 (a, b, c, d, e, idate) values ('p003', 'a', 2, 'n', 4, '2019-02-11');

insert into t0 (a, b, c, d, e, idate) values ('p004', 'a', 2, 'y', 4, '2019-02-11');

insert into t0 (a, b, c, d, e, idate) values ('p005', 'a', 3, 'y', 4, '2019-02-11');

insert into t0 (a, b, c, d, e, idate) values ('p001', 'a', 4, 'n', 4, '2019-02-12');

insert into t0 (a, b, c, d, e, idate) values ('p002', 'a', 5, 'n', 4, '2019-02-12');

insert into t0 (a, b, c, d, e, idate) values ('p003', 'a', 6, 'n', 4, '2019-02-12');

insert into t0 (a, b, c, d, e, idate) values ('p004', 'a', 7, 'y', 4, '2019-02-12');

insert into t0 (a, b, c, d, e, idate) values ('p005', 'a', 8, 'y', 4, '2019-02-12');

insert into t0 (a, b, c, d, e, idate) values ('p001', 'a', 3, 'n', 4, '2019-02-13');

insert into t0 (a, b, c, d, e, idate) values ('p002', 'a', 4, 'n', 4, '2019-02-13');

insert into t0 (a, b, c, d, e, idate) values ('p003', 'a', 5, 'n', 4, '2019-02-13');

insert into t0 (a, b, c, d, e, idate) values ('p004', 'a', 6, 'y', 4, '2019-02-13');

insert into t0 (a, b, c, d, e, idate) values ('p005', 'a', 7, 'y', 4, '2019-02-13');



create table t1 (n INTEGER PRIMARY KEY, f, g, h, i, j, idate);

insert into t1 (f, g, h, i, j, idate) values ('p001', 'b', 6, 'o', 4, '2019-02-15');

insert into t1 (f, g, h, i, j, idate) values ('p002', 'b', 6, 'o', 4, '2019-02-15');

insert into t1 (f, g, h, i, j, idate) values ('p003', 'b', 6, 'i', 4, '2019-02-15');

insert into t1 (f, g, h, i, j, idate) values ('p004', 'a', 9, 'i', 4, '2019-02-16');

insert into t1 (f, g, h, i, j, idate) values ('p005', 'a', 8, 'u', 4, '2019-02-16');

insert into t1 (f, g, h, i, j, idate) values ('p001', 'a', 8, 'u', 4, '2019-02-17');

insert into t1 (f, g, h, i, j, idate) values ('p002', 'a', 5, 'e', 4, '2019-02-17');

insert into t1 (f, g, h, i, j, idate) values ('p003', 'a', 7, 'e', 4, '2019-02-17');

insert into t1 (f, g, h, i, j, idate) values ('p004', 'a', 7, 'b', 4, '2019-02-17');

insert into t1 (f, g, h, i, j, idate) values ('p005', 'a', 3, 'b', 4, '2019-02-18');

insert into t1 (f, g, h, i, j, idate) values ('p001', 'a', 3, 'a', 4, '2019-02-18');

insert into t1 (f, g, h, i, j, idate) values ('p002', 'a', 3, 'a', 4, '2019-02-18');

insert into t1 (f, g, h, i, j, idate) values ('p003', 'a', 5, 'a', 4, '2019-02-18');

insert into t1 (f, g, h, i, j, idate) values ('p004', 'a', 6, 'o', 4, '2019-02-18');

insert into t1 (f, g, h, i, j, idate) values ('p005', 'a', 7, 'o', 4, '2019-02-18');



create table t2 (n INTEGER PRIMARY KEY, f, g, h, i, j, idate);

insert into t2 (f, g, h, i, j, idate) values ('p001', 'b', 6, 'o', 4, '2019-02-15');

insert into t2 (f, g, h, i, j, idate) values ('p002', 'b', 6, 'o', 4, '2019-02-15');

insert into t2 (f, g, h, i, j, idate) values ('p003', 'b', 6, 'i', 4, '2019-02-15');

insert into t2 (f, g, h, i, j, idate) values ('p004', 'a', 9, 'i', 4, '2019-02-16');

insert into t2 (f, g, h, i, j, idate) values ('p005', 'a', 8, 'u', 4, '2019-02-16');

insert into t2 (f, g, h, i, j, idate) values ('p001', 'a', 8, 'u', 4, '2019-02-17');

insert into t2 (f, g, h, i, j, idate) values ('p002', 'a', 5, 'e', 4, '2019-02-17');

insert into t2 (f, g, h, i, j, idate) values ('p003', 'a', 7, 'e', 4, '2019-02-17');

insert into t2 (f, g, h, i, j, idate) values ('p004', 'a', 7, 'b', 4, '2019-02-17');

insert into t2 (f, g, h, i, j, idate) values ('p005', 'a', 3, 'b', 4, '2019-02-18');

insert into t2 (f, g, h, i, j, idate) values ('p001', 'a', 3, 'a', 4, '2019-02-18');

insert into t2 (f, g, h, i, j, idate) values ('p002', 'a', 3, 'a', 4, '2019-02-18');

insert into t2 (f, g, h, i, j, idate) values ('p003', 'a', 5, 'a', 4, '2019-02-18');

insert into t2 (f, g, h, i, j, idate) values ('p004', 'a', 6, 'o', 4, '2019-02-18');

insert into t2 (f, g, h, i, j, idate) values ('p005', 'a', 7, 'o', 4, '2019-02-18');

sqlite> select * from t1;
1|p001|b|6|o|4|2019-02-15
2|p002|b|6|o|4|2019-02-15
3|p003|b|6|i|4|2019-02-15
4|p004|a|9|i|4|2019-02-16
5|p005|a|8|u|4|2019-02-16
6|p001|a|8|u|4|2019-02-17
7|p002|a|5|e|4|2019-02-17
8|p003|a|7|e|4|2019-02-17
9|p004|a|7|b|4|2019-02-17
10|p005|a|3|b|4|2019-02-18
11|p001|a|3|a|4|2019-02-18
12|p002|a|3|a|4|2019-02-18
13|p003|a|5|a|4|2019-02-18
14|p004|a|6|o|4|2019-02-18
15|p005|a|7|o|4|2019-02-18
sqlite> select * from t0;
1|p001|a|1|n|4|2019-02-11
2|p002|a|1|n|4|2019-02-11
3|p003|a|2|n|4|2019-02-11
4|p004|a|2|y|4|2019-02-11
5|p005|a|3|y|4|2019-02-11
6|p001|a|4|n|4|2019-02-12
7|p002|a|5|n|4|2019-02-12
8|p003|a|6|n|4|2019-02-12
9|p004|a|7|y|4|2019-02-12
10|p005|a|8|y|4|2019-02-12
11|p001|a|3|n|4|2019-02-13
12|p002|a|4|n|4|2019-02-13
13|p003|a|5|n|4|2019-02-13
14|p004|a|6|y|4|2019-02-13
15|p005|a|7|y|4|2019-02-13
sqlite> select * from t2;
1|p001|b|6|o|4|2019-02-15
2|p002|b|6|o|4|2019-02-15
3|p003|b|6|i|4|2019-02-15
4|p004|a|9|i|4|2019-02-16
5|p005|a|8|u|4|2019-02-16
6|p001|a|8|u|4|2019-02-17
7|p002|a|5|e|4|2019-02-17
8|p003|a|7|e|4|2019-02-17
9|p004|a|7|b|4|2019-02-17
10|p005|a|3|b|4|2019-02-18
11|p001|a|3|a|4|2019-02-18
12|p002|a|3|a|4|2019-02-18
13|p003|a|5|a|4|2019-02-18
14|p004|a|6|o|4|2019-02-18
15|p005|a|7|o|4|2019-02-18
sqlite> select count(a.n),count(b.n),count(c.n) FROM t0 AS a LEFT JOIN t1 AS b LEFT JOIN t2 AS c;
3375|3375|3375

Huh?  I expected the result:

15|15|15

Thoughts?  Thanks.

josé

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Count error?

Richard Hipp-3
On 11/1/19, Jose Isaias Cabrera <[hidden email]> wrote:
> sqlite> select count(a.n),count(b.n),count(c.n) FROM t0 AS a LEFT JOIN t1 AS
> b LEFT JOIN t2 AS c;
> 3375|3375|3375
>
> Huh?  I expected the result:
>
> 15|15|15

You did a three-way join on tables with 15 rows each.  15x15x15 is 3375.

A LEFT JOIN without an ON clause is just a JOIN.  Or, to view it
another way, it is the same as having "ON true" on each LEFT JOIN.
LEFT JOIN only differs from JOIN when the ON clause evaluates to false
or NULL, in which case the right table of the LEFT JOIN pretends to be
a table of a single row of all NULL values.

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Count error?

Jose Isaias Cabrera-4

Richard Hipp, on Friday, November 1, 2019 11:41 AM, wrote...

>
> On 11/1/19, Jose Isaias Cabrera, on
> > sqlite> select count(a.n),count(b.n),count(c.n) FROM t0 AS a LEFT JOIN t1 AS
> > b LEFT JOIN t2 AS c;
> > 3375|3375|3375
> >
> > Huh?  I expected the result:
> >
> > 15|15|15
>
> You did a three-way join on tables with 15 rows each.  15x15x15 is 3375.
>
> A LEFT JOIN without an ON clause is just a JOIN.  Or, to view it
> another way, it is the same as having "ON true" on each LEFT JOIN.
> LEFT JOIN only differs from JOIN when the ON clause evaluates to false
> or NULL, in which case the right table of the LEFT JOIN pretends to be
> a table of a single row of all NULL values.

Thanks, Dr. Hipp.  Now I understand why the real query hung, and didn't produce a response.  Sorry for the lack of knowledge. :-)  Anyone, out there, how do I get the record count of three tables in one call?  Thanks.

josé
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Count error?

Jose Isaias Cabrera-4

Jose Isaias Cabrera, on Friday, November 1, 2019 11:51 AM, wrote...

>
>
> Richard Hipp, on Friday, November 1, 2019 11:41 AM, wrote...
> >
> > On 11/1/19, Jose Isaias Cabrera, on
> > > sqlite> select count(a.n),count(b.n),count(c.n) FROM t0 AS a LEFT JOIN t1 AS
> > > b LEFT JOIN t2 AS c;
> > > 3375|3375|3375
> > >
> > > Huh?  I expected the result:
> > >
> > > 15|15|15
> >
> > You did a three-way join on tables with 15 rows each.  15x15x15 is 3375.
> >
> > A LEFT JOIN without an ON clause is just a JOIN.  Or, to view it
> > another way, it is the same as having "ON true" on each LEFT JOIN.
> > LEFT JOIN only differs from JOIN when the ON clause evaluates to false
> > or NULL, in which case the right table of the LEFT JOIN pretends to be
> > a table of a single row of all NULL values.
>
> Thanks, Dr. Hipp.  Now I understand why the real query hung, and didn't produce a
> response.  Sorry for the lack of knowledge. :-)  Anyone, out there, how do I get
> the record count of three tables in one call?  Thanks.

So, I got it to work in 3 rows,

select count(n) from t0 UNION ALL Select count(n) FROM t1 UNION ALL SELECT count(n) FROM t2;
15
15
15

But, is there a way to get it to work on one row?  Sorry for the newbie post.  Thanks.

josé
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Count error?

Jim Morris
Using a sub-select should work

select

(select count(n) from t0) as "t0_count",

(select count(n) from t1) as "t1_count",

(select count(n) from t2) as "t2_count"

;

On 11/1/2019 9:07 AM, Jose Isaias Cabrera wrote:

> Jose Isaias Cabrera, on Friday, November 1, 2019 11:51 AM, wrote...
>>
>> Richard Hipp, on Friday, November 1, 2019 11:41 AM, wrote...
>>> On 11/1/19, Jose Isaias Cabrera, on
>>>> sqlite> select count(a.n),count(b.n),count(c.n) FROM t0 AS a LEFT JOIN t1 AS
>>>> b LEFT JOIN t2 AS c;
>>>> 3375|3375|3375
>>>>
>>>> Huh?  I expected the result:
>>>>
>>>> 15|15|15
>>> You did a three-way join on tables with 15 rows each.  15x15x15 is 3375.
>>>
>>> A LEFT JOIN without an ON clause is just a JOIN.  Or, to view it
>>> another way, it is the same as having "ON true" on each LEFT JOIN.
>>> LEFT JOIN only differs from JOIN when the ON clause evaluates to false
>>> or NULL, in which case the right table of the LEFT JOIN pretends to be
>>> a table of a single row of all NULL values.
>> Thanks, Dr. Hipp.  Now I understand why the real query hung, and didn't produce a
>> response.  Sorry for the lack of knowledge. :-)  Anyone, out there, how do I get
>> the record count of three tables in one call?  Thanks.
> So, I got it to work in 3 rows,
>
> select count(n) from t0 UNION ALL Select count(n) FROM t1 UNION ALL SELECT count(n) FROM t2;
> 15
> 15
> 15
>
> But, is there a way to get it to work on one row?  Sorry for the newbie post.  Thanks.
>
> josé
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Count error?

Jose Isaias Cabrera-4


Jim Morris, on Friday, November 1, 2019 12:07 PM, wrote...

>
> Using a sub-select should work
>
> select
>
> (select count(n) from t0) as "t0_count",
>
> (select count(n) from t1) as "t1_count",
>
> (select count(n) from t2) as "t2_count"
>
> ;

Thanks.  Works.

josé

> On 11/1/2019 9:07 AM, Jose Isaias Cabrera wrote:
> > Jose Isaias Cabrera, on Friday, November 1, 2019 11:51 AM, wrote...
> >>
> >> Richard Hipp, on Friday, November 1, 2019 11:41 AM, wrote...
> >>> On 11/1/19, Jose Isaias Cabrera, on
> >>>> sqlite> select count(a.n),count(b.n),count(c.n) FROM t0 AS a LEFT JOIN t1 AS
> >>>> b LEFT JOIN t2 AS c;
> >>>> 3375|3375|3375
> >>>>
> >>>> Huh?  I expected the result:
> >>>>
> >>>> 15|15|15
> >>> You did a three-way join on tables with 15 rows each.  15x15x15 is 3375.
> >>>
> >>> A LEFT JOIN without an ON clause is just a JOIN.  Or, to view it
> >>> another way, it is the same as having "ON true" on each LEFT JOIN.
> >>> LEFT JOIN only differs from JOIN when the ON clause evaluates to false
> >>> or NULL, in which case the right table of the LEFT JOIN pretends to be
> >>> a table of a single row of all NULL values.
> >> Thanks, Dr. Hipp.  Now I understand why the real query hung, and didn't produce a
> >> response.  Sorry for the lack of knowledge. :-)  Anyone, out there, how do I get
> >> the record count of three tables in one call?  Thanks.
> > So, I got it to work in 3 rows,
> >
> > select count(n) from t0 UNION ALL Select count(n) FROM t1 UNION ALL SELECT count(n) FROM t2;
> > 15
> > 15
> > 15
> >
> > But, is there a way to get it to work on one row?  Sorry for the newbie post.  Thanks.
> >
> > josé
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users