I've a problem to in building a query for python sqlite3 to do the following:

Count a word which appears in columns, if word appears more than 1 time count one. I've attach a picture to illustrate my table format.

Tried this but the result still count duplicate value with same ID.

"SELECT  id,  value, count(value) FROM table WHERE type like'%hi%' GROUP BY value ORDER BY COUNT(*)<1 DESC"

enter image description here

[enter image description here][2]

THE RESULT NEED TO BE LIKE : [2]: https://i.stack.imgur.com/226ei.png

CodePudding user response：

Hi all you need can be achieved with GROUP BY clause. This should help:

SELECT
     id
    ,value
    ,1 AS cnt
FROM table
GROUP BY id, value
ORDER BY id

CodePudding user response：

What you're looking for is DISTINCT clause or GROUP BY as mentioned by Peter.

for GROUP BY use this syntax:

SELECT
     id
    ,value
    ,1 AS cnt
FROM table
GROUP BY id, value

for DISTINCT use this one:

SELECT DISTINCT
     id
    ,value
    ,1 AS cnt
FROM table