CODEWITHSUNDEEP
sqlsqlite
Asnag
Asnag

Reputation: 90

Obtaining the percentage in sqllite

I made a query with the following statement :

select mood, count(*) * 100/ (select count(*) from entry)from entry group by mood having data>data-30 order by mood asc

mood is an integer from 0 to 2

the output is : 

             mood         count       


              0            96,55

              1            3,44

is there a way to add a row with mood 2 count 0?

Upvotes: 1

Views: 96

Answers (1)

UltraCommit
UltraCommit

Reputation: 2286

  SELECT   MOOD, SUM (COUNTER) TOTAL
    FROM   (  SELECT   0 MOOD, 0 COUNTER FROM DUAL
            UNION ALL
              SELECT   1 MOOD, 0 COUNTER FROM DUAL
            UNION ALL
              SELECT   2 MOOD, 0 COUNTER FROM DUAL
            UNION ALL
              SELECT   MOOD,   COUNT ( * )
                             * 100.0
                             / (SELECT   COUNT ( * )
                                  FROM   ENTRY
                                 WHERE   DATA > DATE ('now') - 30)
                FROM   (SELECT   *
                          FROM   ENTRY
                         WHERE   DATA > DATE ('now') - 30)
            GROUP BY   MOOD, DATA)
GROUP BY   MOOD
ORDER BY   MOOD ASC;

You have to enumerate (0, 1, 2, .....) all the possible numbers, associating a counter = 0. Then, you sum the counters grouping by mood.

Please note that your condition having data>data-30 is absurd. You have to select from ENTRY all the records satisfying the condition data > date('now') - 30, for example.

SQLite: A VIEW named "dual" that works the same as the Oracle "dual" table can be created as follows: "CREATE VIEW dual AS SELECT 'x' AS dummy;"

Upvotes: 1

Related Questions