You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@metamodel.apache.org by Kasper Sørensen <i....@gmail.com> on 2018/02/01 04:43:44 UTC
Re: count / distinct SQL
There's no direct equivalent to COUNT(DISTINCT x)) in MetaModel. But you
could get the same result using a subquery, and most database engines would
probably also plan the query in the same way. Basically you would then do:
SELECT COUNT(*) FROM (SELECT DISTINCT text1 FROM small_table)
2018-01-31 5:16 GMT-08:00 Dana Borger <Da...@sas.com>:
>
> Suppose I have a table and rows like (fwiw, Postgres):
>
> CREATE TABLE small_table (
> key1 INTEGER NOT NULL,
> text1 VARCHAR(10),
> text2 VARCHAR(10),
> text3 VARCHAR(10),
> text4 VARCHAR(10)
> );
> ALTER TABLE small_table ADD CONSTRAINT small_table_pk PRIMARY KEY (key1);
> INSERT INTO small_table VALUES (1, 'a', 'a', 'a', 'a');
> INSERT INTO small_table VALUES (2, 'a', 'b', 'b', 'b');
> INSERT INTO small_table VALUES (3, 'a', 'b', 'c', 'c');
> INSERT INTO small_table VALUES (4, 'a', 'b', 'c', 'd');
>
>
> Is there a way with the MM API to construct this query:
>
> SELECT COUNT(DISTINCT text1) FROM small_table;
>
> ?
>
> which returns (count) = (1) which is what i want. ‘text1’ has 1 distinct
> value (‘a’).
>
>
> Using the API this way, it produces a different query than what I want:
>
> Table table = connection.getTableByName("small_table");
> Query q = new Query();
> q.from(table).select("text1");
> q.groupBy("text1");
> q.selectDistinct().selectCount();
>
> —>
>
> SELECT DISTINCT “small_table”."text1", COUNT(*)
> FROM ”small_table”
> GROUP BY “small_table”."text1"
>
> which returns: (text1,count) = (a, 4) [ not what i want ]
>
>
> Thanks,
> Dana
>
>
RE: count / distinct SQL
Posted by Vijay Kumar Jalagari <ja...@adobe.com.INVALID>.
e
-----Original Message-----
From: Vijay Kumar Jalagari <ja...@adobe.com.INVALID>
Sent: Wednesday, October 31, 2018 2:57 PM
To: dev@metamodel.apache.org
Subject: RE: count / distinct SQL
e
-----Original Message-----
From: Kasper Sørensen <i....@gmail.com>
Sent: Thursday, February 1, 2018 10:14 AM
To: dev@metamodel.apache.org
Subject: Re: count / distinct SQL
There's no direct equivalent to COUNT(DISTINCT x)) in MetaModel. But you could get the same result using a subquery, and most database engines would probably also plan the query in the same way. Basically you would then do:
SELECT COUNT(*) FROM (SELECT DISTINCT text1 FROM small_table)
2018-01-31 5:16 GMT-08:00 Dana Borger <Da...@sas.com>:
>
> Suppose I have a table and rows like (fwiw, Postgres):
>
> CREATE TABLE small_table (
> key1 INTEGER NOT NULL,
> text1 VARCHAR(10),
> text2 VARCHAR(10),
> text3 VARCHAR(10),
> text4 VARCHAR(10)
> );
> ALTER TABLE small_table ADD CONSTRAINT small_table_pk PRIMARY KEY
> (key1); INSERT INTO small_table VALUES (1, 'a', 'a', 'a', 'a'); INSERT
> INTO small_table VALUES (2, 'a', 'b', 'b', 'b'); INSERT INTO
> small_table VALUES (3, 'a', 'b', 'c', 'c'); INSERT INTO small_table
> VALUES (4, 'a', 'b', 'c', 'd');
>
>
> Is there a way with the MM API to construct this query:
>
> SELECT COUNT(DISTINCT text1) FROM small_table;
>
> ?
>
> which returns (count) = (1) which is what i want. ‘text1’ has 1
> distinct value (‘a’).
>
>
> Using the API this way, it produces a different query than what I want:
>
> Table table = connection.getTableByName("small_table");
> Query q = new Query();
> q.from(table).select("text1");
> q.groupBy("text1");
> q.selectDistinct().selectCount();
>
> —>
>
> SELECT DISTINCT “small_table”."text1", COUNT(*) FROM ”small_table”
> GROUP BY “small_table”."text1"
>
> which returns: (text1,count) = (a, 4) [ not what i want ]
>
>
> Thanks,
> Dana
>
>
RE: count / distinct SQL
Posted by Vijay Kumar Jalagari <ja...@adobe.com.INVALID>.
e
-----Original Message-----
From: Kasper Sørensen <i....@gmail.com>
Sent: Thursday, February 1, 2018 10:14 AM
To: dev@metamodel.apache.org
Subject: Re: count / distinct SQL
There's no direct equivalent to COUNT(DISTINCT x)) in MetaModel. But you could get the same result using a subquery, and most database engines would probably also plan the query in the same way. Basically you would then do:
SELECT COUNT(*) FROM (SELECT DISTINCT text1 FROM small_table)
2018-01-31 5:16 GMT-08:00 Dana Borger <Da...@sas.com>:
>
> Suppose I have a table and rows like (fwiw, Postgres):
>
> CREATE TABLE small_table (
> key1 INTEGER NOT NULL,
> text1 VARCHAR(10),
> text2 VARCHAR(10),
> text3 VARCHAR(10),
> text4 VARCHAR(10)
> );
> ALTER TABLE small_table ADD CONSTRAINT small_table_pk PRIMARY KEY
> (key1); INSERT INTO small_table VALUES (1, 'a', 'a', 'a', 'a'); INSERT
> INTO small_table VALUES (2, 'a', 'b', 'b', 'b'); INSERT INTO
> small_table VALUES (3, 'a', 'b', 'c', 'c'); INSERT INTO small_table
> VALUES (4, 'a', 'b', 'c', 'd');
>
>
> Is there a way with the MM API to construct this query:
>
> SELECT COUNT(DISTINCT text1) FROM small_table;
>
> ?
>
> which returns (count) = (1) which is what i want. ‘text1’ has 1
> distinct value (‘a’).
>
>
> Using the API this way, it produces a different query than what I want:
>
> Table table = connection.getTableByName("small_table");
> Query q = new Query();
> q.from(table).select("text1");
> q.groupBy("text1");
> q.selectDistinct().selectCount();
>
> —>
>
> SELECT DISTINCT “small_table”."text1", COUNT(*) FROM ”small_table”
> GROUP BY “small_table”."text1"
>
> which returns: (text1,count) = (a, 4) [ not what i want ]
>
>
> Thanks,
> Dana
>
>