Hi Tom,
Its a pretty gruesome bit of sql...
C:\Joinerysoft\JMS\dev\trunk> sqlite3 tst.db
SQLite version 3.4.0
Enter ".help" for instructions
sqlite> create table List( Code text );
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>
sqlite> select
...>case rowid>(select rowid from List as L2 where L1.Code=L2.Code limit 1)
...>when 0 then L1.Code
...>else null end,
...>case rowid>(select rowid from List as L2 where L1.Code=L2.Code limit 1)
...>when 0 then
...> (select count(Code) from List as L2 where l1.Code=L2.Code)
...>else null end
...>from List as L1;
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]
-----------------------------------------------------------------------------
