CODEWITHSUNDEEP
sqlsqlite
Rudy Gamberini
Rudy Gamberini

Reputation: 25

Sqlite Get counts of all distinct values across a row

For a personal end of the year project I've scraped my attendance off the school website hoping to do some form of visualization of the data. I've now gotten stuck transforming that data into the form I need it in.

Currently my database looks like this

Date,One,Two,Three,Four,Five,Six,Seven,Eight,Nine,Dee
2014-09-03,P,P,P,P,AU,AU,P,T*,AU,P
2014-09-04,P,P,P,P,N/A,AU,P,T*,N/A,P
2014-09-05,P,P,P,P,AU,AU,P,P,P,P
2014-09-09,P,P,P,P,AU,AU,P,P,AU,P
2014-09-11,AU,AU,P,AU,AU,P,AU,AU,AU,P
2014-09-15,P,P,P,P,AU,P,P,P,AU,P
2014-09-17,P,P,P,P,AU,AU,P,P,AU,P

The columns are each period,and each one has an indicator of my presence. My question is, is it possible to turn that into something like this using only sqlite?

Date,P,AU,T*,N/A
2014-09-03,6,3,1,0
2014-09-04,6,1,1,2
2014-09-05,8,2,0,0
2014-09-09,7,3,0,0
2014-09-11,3,7,0,0
2014-09-15,8,2,0,0
2014-09-17,7,3,0,0
2014-09-19,9,1,0,0

Counting each occurence of a value across the row.

Upvotes: 1

Views: 47

Answers (1)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35790

Something like this:

select date,
       case when one = 'p' then 1 else 0 end +
       case when two = 'p' then 1 else 0 end +
       ...
       case when dee = 'p' then 1 else 0 end as p,


       case when one = 'au' then 1 else 0 end +
       case when two = 'au' then 1 else 0 end +
       ...
       case when dee = 'au' then 1 else 0 end as au,

       ...
from table

Upvotes: 1

Related Questions