How do I create a table that counts the number of times the same text occurs in a field in SQLite?-CodePudding

I have a table that has a text column. I can generate a table that can separate the number of distinct rows like this:

DROP TABLE IF EXISTS msgs;
CREATE TABLE msgs (
    Msg TEXT
);
INSERT INTO msgs(Msg) SELECT DISTINCT Message FROM Log;

But I'd like to add a column into msgs that states how many times that Msg actually occurred in the Log table. I tried reading SQLite COUNT page, but I'm not clear as how I'd put that into the INSERT INTO statement. I'm new to more advanced (intermediate?) stuff in SQL, so I'm finding it hard to wrap my head around it. Any ideas as how I'd do this? Any reference material that would help me figure this out would also be appreciated.

CodePudding user response：

Instead of DISTINCT, you should use GROUP BY and an aggregated function, in this case, COUNT()

DROP TABLE IF EXISTS msgs;
CREATE TABLE msgs (
    Msg TEXT
    Msg_count int
);
    INSERT INTO msgs(Msg, msg_count)
    SELECT Message, count(Message) as msg_count FROM Log
    
    group by Message;

CodePudding user response：

You can aggregate the data set using GROUP BY. For example:

CREATE TABLE msgs (
    Msg TEXT,
    cnt int    
);

INSERT INTO msgs(Msg, cnt) 
SELECT Message, count(*) 
FROM Log 
GROUP BY Msg;