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 <dana.bor...@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
>
>
