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
