CODEWITHSUNDEEP
sqlsqlitegroup-byaggregate-functionspercentage
GreenTriangle
GreenTriangle

Reputation: 2460

How to compute percentage in each group?

I have a table called events, with 5000 entries.

Each event has a recorded_on field that might be null or might not be.

Each event also has an artist field, which is one of 10 distinct values.

Is it within the abilities of SQL (SQLite specifically) to issue a single query that will return the names of artists and what percentage of their events have null as the value for recorded_on? As in

ARTIST | PERCENTAGE RECORDED
------------------------------
Joe    | 49.2
Sarah  | 17.8
Mike   | 83.0

Upvotes: 1

Views: 64

Answers (2)

CL.
CL.

Reputation: 180172

To get one output row for each group, use GROUP BY. recorded_on IS NULL is a boolean expression whose result is either 1 or 0:

SELECT artist,
       100.0 * sum(recorded_on IS NULL) / count(*) AS "percentage recorded"
FROM events
GROUP BY artist;

Upvotes: 1

Waqar Ul Aziz
Waqar Ul Aziz

Reputation: 197

select 
    artist,
    ( COUNT(
        CASE recorded_on
          WHEN NULL THEN 1
          ELSE NULL 
        END
    ) / COUNT(*) ) * 100 as percentage
 from event group by artist

Upvotes: 0

Related Questions