e -----Original Message----- From: Vijay Kumar Jalagari <[email protected]> Sent: Wednesday, October 31, 2018 2:57 PM To: [email protected] Subject: RE: count / distinct SQL
e -----Original Message----- From: Kasper Sørensen <[email protected]> Sent: Thursday, February 1, 2018 10:14 AM To: [email protected] 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 <[email protected]>: > > 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 > >
