CODEWITHSUNDEEP
sqlsqlite
Prateek
Prateek

Reputation: 4013

How to get unique substring count from sqlite column?

From the data below , I would like to fetch the count of unique dates , ignoring the time value. 

"01.09.2017 06.16"
"01.09.2017 06.17"
"01.09.2017 06.17"
"01.09.2017 06.32"
"26.10.2017 13.58"
"26.10.2017 13.59"
"26.10.2017 13.59"
"26.10.2017 14.02"
"18.01.2018 16.26"
"18.01.2018 16.26"
"18.01.2018 16.29"
"18.01.2018 16.29"
"18.01.2018 16.29"
"29.01.2018 15.45"
"29.01.2018 15.57"
"29.01.2018 15.57"
"29.01.2018 15.58"
"29.01.2018 15.58"

The resulting output for the sql query should be 4

Please ignore the double quotes, I have take this data from a column name PWhen and table name PData.

Upvotes: 1

Views: 830

Answers (2)

Bibin Mathew
Bibin Mathew

Reputation: 465

Hope this helps you

  SELECT SUBSTR(PWhen,1,INSTR(PWhen,' ')),COUNT(*) from PDate GROUP BY
  SUBSTR(PWhen,1,INSTR(PWhen,' '));

To get the Unique list of dates 

  SELECT COUNT(DISTINCT SUBSTR(PWhen,1,INSTR(PWhen,' '))) from PDate;

Upvotes: 1

Yunnosch
Yunnosch

Reputation: 26703

In order to get the count of unique dates (4) for the sample input,
count the distinct dates, as reflected by the substring until the first blank.

select count(distinct substr(info, 1, instr (info,' '))) from strings;

Output:

4

MCVE I constructed from your syntax-free sample data:

CREATE TABLE strings (info varchar(20));
INSERT INTO "strings" VALUES('01.09.2017 06.16');
INSERT INTO "strings" VALUES('01.09.2017 06.17');
INSERT INTO "strings" VALUES('01.09.2017 06.17');
INSERT INTO "strings" VALUES('01.09.2017 06.32');
INSERT INTO "strings" VALUES('26.10.2017 13.58');
INSERT INTO "strings" VALUES('26.10.2017 13.59');
INSERT INTO "strings" VALUES('26.10.2017 13.59');
INSERT INTO "strings" VALUES('26.10.2017 14.02');
INSERT INTO "strings" VALUES('18.01.2018 16.26');
INSERT INTO "strings" VALUES('18.01.2018 16.26');
INSERT INTO "strings" VALUES('18.01.2018 16.29');
INSERT INTO "strings" VALUES('18.01.2018 16.29');
INSERT INTO "strings" VALUES('18.01.2018 16.29');
INSERT INTO "strings" VALUES('29.01.2018 15.45');
INSERT INTO "strings" VALUES('29.01.2018 15.57');
INSERT INTO "strings" VALUES('29.01.2018 15.57');
INSERT INTO "strings" VALUES('29.01.2018 15.58');
INSERT INTO "strings" VALUES('29.01.2018 15.58');

Upvotes: 1

Related Questions