Sqlite Get counts of all distinct values across a row

Question

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.

Giorgi Nakeuri · Accepted Answer · 2015-06-13 09:01:03Z

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

Collectives™ on Stack Overflow

Sqlite Get counts of all distinct values across a row

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related