Re: [sqlite] count from 2 tables
The answer to you exact question is "Yes". Didn't help much did it?
But I will at least start off by giving some hints. You need to do an inner
join on the common column (service_no). You need to use the HAVING clause
to select only those entries with more than 2 entries in the result table
(which requires a GROUP BY phrase). And you then need to count the number
of distinct service_no values which pass that test.
I am somewhat hesitant to post the actual code. Why? Because it seems like
something which an SQL person should know. But I will be somewhat more
helpful that I normally am, and post my solution.
select count(distinct a.service_no) as count
from service_table as a
inner join service_port_table as b
on a.service_no = b.service_no
where a.service_type='e-lan'
group by a.service_no
having count(a.service_no) > 2
;
The output is a bit difficult, for me, to read when I enter that in the
sqlite3 program, so I also did:
.separator "\t"
.headers on
On Tue, Sep 3, 2013 at 6:00 PM, ChingChang Hsiao <
chingchang.hs...@overturenetworks.com> wrote:
>
> There are 2 related table above. There are service_no 1,2,3,5. Service
> 1,2,3 are type e-line, service 5 is type e-lan. Service 1,2,3 own 2
> service_port(sp_no 1,2). Service 5 owns 3 service_port(sp_no 1,2,3).
>
> What is the count of services have more than 2 service_port(sp) and type
> is e-lan? The answer is 1. Could it be described as one sql statement?
>
>
>
> CREATE TABLE service_table (
> service_nameTEXT, /* name of the service (unique) */
> service_no INTEGER, /* internal service number, assigned by
> the system. */
> service_typeTEXT, /* Type of Service. Should be one of
> the following:
> e-lan, e-line, e-tree, ip-forward
> The default should be e-line */
> learning_enabled INTEGER, /* Learning Enabled 0=no 1=yes 1=default
> */
> col_status TEXT,
> cfg_status TEXT);
> INSERT INTO "service_table" VALUES('e-line-1',1,'e-line',1,'','committed');
> INSERT INTO "service_table" VALUES('test',2,'e-line',1,'','committed');
> INSERT INTO "service_table"
> VALUES('uni1-evc1',3,'e-line',1,'','committed');
> INSERT INTO "service_table"
> VALUES('zero-touch-test',5,'e-lan',1,'','committed');
>
>
> CREATE TABLE service_port_table(
> sp_no INTEGER, /* Range 1 to 256 */
> service_no INTEGER, /* The Service this service port belongs
> to */
> sp_idx INTEGER, /* This is used to identify the
> classification rule:
> This should range from 1 to 1 */
> if_idx INTEGER); /* Interface Index of the underlying
> interface */
>
> INSERT INTO "service_port_table" VALUES(1,1,1,26);
> INSERT INTO "service_port_table" VALUES(2,1,2,43);
> INSERT INTO "service_port_table" VALUES(1,2,3,53);
> INSERT INTO "service_port_table" VALUES(2,2,4,26);
> INSERT INTO "service_port_table" VALUES(1,3,5,29);
> INSERT INTO "service_port_table" VALUES(2,3,6,54);
> INSERT INTO "service_port_table" VALUES(1,5,20,12);
> INSERT INTO "service_port_table" VALUES(2,5,21,58);
> INSERT INTO "service_port_table" VALUES(3,5,22,27);
>
>
--
As of next week, passwords will be entered in Morse code.
Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] count from 2 tables
There are 2 related table above. There are service_no 1,2,3,5. Service 1,2,3
are type e-line, service 5 is type e-lan. Service 1,2,3 own 2
service_port(sp_no 1,2). Service 5 owns 3 service_port(sp_no 1,2,3).
What is the count of services have more than 2 service_port(sp) and type is
e-lan? The answer is 1. Could it be described as one sql statement?
CREATE TABLE service_table (
service_nameTEXT, /* name of the service (unique) */
service_no INTEGER, /* internal service number, assigned by the
system. */
service_typeTEXT, /* Type of Service. Should be one of
the following:
e-lan, e-line, e-tree, ip-forward
The default should be e-line */
learning_enabled INTEGER, /* Learning Enabled 0=no 1=yes 1=default */
col_status TEXT,
cfg_status TEXT);
INSERT INTO "service_table" VALUES('e-line-1',1,'e-line',1,'','committed');
INSERT INTO "service_table" VALUES('test',2,'e-line',1,'','committed');
INSERT INTO "service_table" VALUES('uni1-evc1',3,'e-line',1,'','committed');
INSERT INTO "service_table"
VALUES('zero-touch-test',5,'e-lan',1,'','committed');
CREATE TABLE service_port_table(
sp_no INTEGER, /* Range 1 to 256 */
service_no INTEGER, /* The Service this service port belongs to */
sp_idx INTEGER, /* This is used to identify the
classification rule:
This should range from 1 to 1 */
if_idx INTEGER, /* Interface Index of the underlying interface */
INSERT INTO "service_port_table" VALUES(1,1,1,26);
INSERT INTO "service_port_table" VALUES(2,1,2,43);
INSERT INTO "service_port_table" VALUES(1,2,3,53);
INSERT INTO "service_port_table" VALUES(2,2,4,26);
INSERT INTO "service_port_table" VALUES(1,3,5,29);
INSERT INTO "service_port_table" VALUES(2,3,6,54,);
INSERT INTO "service_port_table" VALUES(1,5,20,12);
INSERT INTO "service_port_table" VALUES(2,5,21,58);
INSERT INTO "service_port_table" VALUES(3,5,22,27);
This email and attachments may contain privileged or confidential information
intended only for the addressee(s) indicated. The sender does not waive any of
its rights, privileges or protections respecting this information. If you are
not the named addressee, an employee, or agent responsible for sending this
message to the named addressee (or this message was received by mistake), you
are not authorized to read, print, retain, copy or disseminate this message or
any part of it. If received in error, please notify us immediately by e-mail,
discard any paper copies and delete all electronic files of the email.
Computer viruses can be transmitted via email. The recipient should check this
email and any attachments for viruses. Email transmission cannot be guaranteed
to be secured or error-free as information could be intercepted, corrupted,
lost, destroyed, arrive late or incomplete, or contain viruses. The sender
accepts no liability for any damage caused by any transmitted viruses or errors
or omissions in the contents of this message.
Overture Networks, Inc. 637 Davis Drive, Morrisville, NC USA 27560
www.overturenetworks.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] count from 2 tables
On 9/3/2013 6:52 PM, ChingChang Hsiao wrote: What is the count of services have more than 2 service_port(sp) and type is e-lan? The answer is 1. Could it be described as one sql statement? select count(*) from service_table s where service_type = 'e-lan' and (select count(*) from service_port_table sp where s.service_no = sp.service_no) > 2; -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] count from 2 tables
CREATE TABLE service_table (
service_nameTEXT, /* name of the service (unique) */
service_no INTEGER, /* internal service number, assigned by the
system. */
service_typeTEXT, /* Type of Service. Should be one of
the following:
e-lan, e-line, e-tree, ip-forward
The default should be e-line */
learning_enabled INTEGER, /* Learning Enabled 0=no 1=yes 1=default */
col_status TEXT,
cfg_status TEXT);
INSERT INTO "service_table" VALUES('e-line-1',1,'e-line',1,'','committed');
INSERT INTO "service_table" VALUES('test',2,'e-line',1,'','committed');
INSERT INTO "service_table" VALUES('uni1-evc1',3,'e-line',1,'','committed');
INSERT INTO "service_table"
VALUES('zero-touch-test',5,'e-lan',1,'','committed');
CREATE TABLE service_port_table(
sp_no INTEGER, /* Range 1 to 256 */
service_no INTEGER, /* The Service this service port belongs to */
sp_idx INTEGER, /* This is used to identify the
classification rule:
This should range from 1 to 1 */
if_idx INTEGER, /* Interface Index of the underlying interface */
INSERT INTO "service_port_table" VALUES(1,1,1,26);
INSERT INTO "service_port_table" VALUES(2,1,2,43);
INSERT INTO "service_port_table" VALUES(1,2,3,53);
INSERT INTO "service_port_table" VALUES(2,2,4,26);
INSERT INTO "service_port_table" VALUES(1,3,5,29);
INSERT INTO "service_port_table" VALUES(2,3,6,54,);
INSERT INTO "service_port_table" VALUES(1,5,20,12);
INSERT INTO "service_port_table" VALUES(2,5,21,58);
INSERT INTO "service_port_table" VALUES(3,5,22,27);
There are 2 related table above. There are service_no 1,2,3,5. Service 1,2,3
are type e-line, service 5 is type e-lan. Service 1,2,3 own 2
service_port(sp_no 1,2). Service 5 owns 3 service_port(sp_no 1,2,3).
What is the count of services have more than 2 service_port(sp) and type is
e-lan? The answer is 1. Could it be described as one sql statement?
Thanks
ChingChang
This email and attachments may contain privileged or confidential information
intended only for the addressee(s) indicated. The sender does not waive any of
its rights, privileges or protections respecting this information. If you are
not the named addressee, an employee, or agent responsible for sending this
message to the named addressee (or this message was received by mistake), you
are not authorized to read, print, retain, copy or disseminate this message or
any part of it. If received in error, please notify us immediately by e-mail,
discard any paper copies and delete all electronic files of the email.
Computer viruses can be transmitted via email. The recipient should check this
email and any attachments for viruses. Email transmission cannot be guaranteed
to be secured or error-free as information could be intercepted, corrupted,
lost, destroyed, arrive late or incomplete, or contain viruses. The sender
accepts no liability for any damage caused by any transmitted viruses or errors
or omissions in the contents of this message.
Overture Networks, Inc. 637 Davis Drive, Morrisville, NC USA 27560
www.overturenetworks.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] count from 2 tables
CREATE TABLE service_table (
service_nameTEXT, /* name of the service (unique) */
service_no INTEGER, /* internal service number, assigned by the
system. */
service_typeTEXT, /* Type of Service. Should be one of
the following:
e-lan, e-line, e-tree, ip-forward
The default should be e-line */
learning_enabled INTEGER, /* Learning Enabled 0=no 1=yes 1=default */
col_status TEXT,
cfg_status TEXT);
INSERT INTO "service_table" VALUES('e-line-1',1,'e-line',1,'','committed');
INSERT INTO "service_table" VALUES('test',2,'e-line',1,'','committed');
INSERT INTO "service_table" VALUES('uni1-evc1',3,'e-line',1,'','committed');
INSERT INTO "service_table"
VALUES('zero-touch-test',5,'e-lan',1,'','committed');
CREATE TABLE service_port_table(
sp_no INTEGER, /* Range 1 to 256 */
service_no INTEGER, /* The Service this service port belongs to */
sp_idx INTEGER, /* This is used to identify the
classification rule:
This should range from 1 to 1 */
if_idx INTEGER, /* Interface Index of the underlying interface */
INSERT INTO "service_port_table" VALUES(1,1,1,26);
INSERT INTO "service_port_table" VALUES(2,1,2,43);
INSERT INTO "service_port_table" VALUES(1,2,3,53);
INSERT INTO "service_port_table" VALUES(2,2,4,26);
INSERT INTO "service_port_table" VALUES(1,3,5,29);
INSERT INTO "service_port_table" VALUES(2,3,6,54,);
INSERT INTO "service_port_table" VALUES(1,5,20,12);
INSERT INTO "service_port_table" VALUES(2,5,21,58);
INSERT INTO "service_port_table" VALUES(3,5,22,27);
There are 2 related table above. There are service_no 1,2,3,5. Service 1,2,3
are type e-line, service 5 is type e-lan. Service 1,2,3 own 2
service_port(sp_no 1,2). Service 5 owns 3 service_port(sp_no 1,2,3).
What is the count of services have more than 2 service_port(sp) and type is
e-lan? The answer is 1. Could it be described as one sql statement?
Thanks
ChingChang
This email and attachments may contain privileged or confidential information
intended only for the addressee(s) indicated. The sender does not waive any of
its rights, privileges or protections respecting this information. If you are
not the named addressee, an employee, or agent responsible for sending this
message to the named addressee (or this message was received by mistake), you
are not authorized to read, print, retain, copy or disseminate this message or
any part of it. If received in error, please notify us immediately by e-mail,
discard any paper copies and delete all electronic files of the email.
Computer viruses can be transmitted via email. The recipient should check this
email and any attachments for viruses. Email transmission cannot be guaranteed
to be secured or error-free as information could be intercepted, corrupted,
lost, destroyed, arrive late or incomplete, or contain viruses. The sender
accepts no liability for any damage caused by any transmitted viruses or errors
or omissions in the contents of this message.
Overture Networks, Inc. 637 Davis Drive, Morrisville, NC USA 27560
www.overturenetworks.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
