count / distinct SQL

[Dana Borger]

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