Re: [sqlite] Sparse matrix

[Simon Davies]

Hi Tom,

Here's a less gruesome version - no cases. I've given no thought to
performance comparisons.

C:\Joinerysoft\JMS\dev\trunk> sqlite3 tst.db
SQLite version 3.4.0
Enter ".help" for instructions

sqlite> insert into List values( 'a' );
sqlite> insert into List values( 'a' );
sqlite> insert into List values( 'a' );
sqlite> insert into List values( 'a' );
sqlite> insert into List values( 'b' );
sqlite> insert into List values( 'b' );
sqlite> insert into List values( 'c' );
sqlite> insert into List values( 'c' );
sqlite> insert into List values( 'c' );
sqlite> select v.Code, v.count from
sqlite>         List as L
sqlite> left join
sqlite>         (select (select L1.rowid from List as L1 where
sqlite>
L1.Code=L2.Code limit 1) as id,
sqlite>                 Code,
sqlite>                 count(Code) as count from List as L2 group by Code) as v
sqlite> on L.rowid=v.id order by L.rowid;
a|4
|
|
|
b|2
|
c|3
|

sqlite>
sqlite>

Rgds,
Simon

On 22/08/07, T&B <[EMAIL PROTECTED]> wrote:
> Hi all,
>
> OK, this one has me stumped. I've been staring at it too long.
>
> I have a table with a column like this:
>
> Code
> a
> a
> a
> a
> b
> b
> c
> c
> c
>
> I want to output two columns, one showing the Code, another showing
> the count of each code, but with a null wherever it was a repeat,
> like this:
>
> Code   Count
> a      4
> .
> .
> .
> b      2
> .
> c      3
> .
> .
>
> (Note I've used . to show a null)
>
> Any ideas?
>
> I can simply do this to show the count of each, but it doesn't show
> the blank rows:
>
> select Code, count(*) as Count from List group by Code;
>
> Here's the test table:
>
> create table List( Code text );
> insert into List values( 'a' );
> insert into List values( 'a' );
> insert into List values( 'a' );
> insert into List values( 'a' );
> insert into List values( 'b' );
> insert into List values( 'b' );
> insert into List values( 'c' );
> insert into List values( 'c' );
> insert into List values( 'c' );
>
> Thanks,
> Tom
>
>
> -----------------------------------------------------------------------------
> To unsubscribe, send email to [EMAIL PROTECTED]
> -----------------------------------------------------------------------------
>
>

-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------