CODEWITHSUNDEEP
sqlsqlite
Jashwant
Jashwant

Reputation: 29025

Sqlite 3: select and count together with group by and without group by

Ahead of this question Sqlite 3 Insert and Replace fails on more than 1 unique column

I have a table with schema,

CREATE TABLE tbl_poll ( 
    id         INTEGER PRIMARY KEY AUTOINCREMENT,
    poll_id    STRING NOT NULL,
    ip_address STRING NOT NULL,
    opt        STRING NULL,
    CONSTRAINT 'unique_vote_per_poll_per_ip_address' UNIQUE ( poll_id, ip_address )  ON CONFLICT REPLACE 
);

When I do,

select opt,count(opt) as count from tbl_poll where poll_id = 'jsfw' group by opt

Result is

opt            count
0                4
2                2 
3                2

i.e. 4 users have selected 0 option, 2 and 3 option is selected by 2 and 2 users respectively.

Is there any way so I can get a result like following

   opt            count    percent
    0                4        0.5       
    2                2        0.25 
    3                2        0.25

where percent = count / total count

If I can get total count i.e. (4+2+2 = 8 ) that will solve my problem too.

I have tried this,

   select opt,count(opt) as count from tbl_poll where poll_id = 'jsfw'

but it doesn't work as no of columns are not same.

Upvotes: 3

Views: 4284

Answers (2)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115650

SELECT opt
     , COUNT(*) AS count
     , ROUND(CAST(COUNT(*) AS REAL)/total, 2) AS percent 
FROM tbl_poll 
  CROSS JOIN
    ( SELECT COUNT(*) AS total 
      FROM tbl_poll 
      WHERE poll_id = 'jsfw' 
    ) AS t
WHERE poll_id = 'jsfw' 
GROUP BY opt ;

Upvotes: 3

Ricardo Souza
Ricardo Souza

Reputation: 16468

If you know all of the possible values from opt, you can use CASE WHEN statement:

SELECT COUNT(opt) as total, SUM(CASE WHEN opt = '0' OR opt IS NULL OR TRIM(opt) = '' THEN 1 ELSE 0 END) as total0, SUM(CASE WHEN opt = '1' THEN 1 ELSE 0 END) as total1, ... FROM tbl_poll WHERE poll_id = 'jsfw'

This will give you:

total    total0    total1    ...
8        4         0         ...

Let me know if this isn't a closed set of options.

The sintax follows:

CASE WHEN condition THEN result_for_true ELSE result_for_false END
CASE WHEN condition1 THEN result_for_1 WHEN condition2 THEN result_for_2 ELSE result_for_false_on_all END

Upvotes: 0

Related Questions