2011/7/6 e-mail mgbg25171 <mgbg25...@blueyonder.co.uk>:
> 1990 1991 year <===== dimension
>
> north sales 10 8
> cogs (5) (4)
> south sales 6 5
> cogs (2) (1)
>
> region line item <========== dimensions
create table stat(region, item, year, value);
insert into stat values("north","sales",1990,10);
insert into stat values("north","sales",1991,8);
:
insert into stat values("south","cogs",1991,-1);
> I just want to be able to specify different dimensional values and get back
> rectangles of data that I can manipulate. I've already done the manipulation
> stuff so it's just how to layout the data.
> Query examples would be...
>
> total = north + south returning 9, 8
select region,sum(value) as total from stat group by region;
> total cogs = north.cogs + south.cogs returning -7, -5
select year,sum(value) as total from stat where item="cogs" group by year;
> 1990 returning 10,-5,6,-2
select region,item,value as total from stat where year=1990;
> north:1991 returning 8,-4
select item,value as total from stat where region="north" and year=1991;
> north:1990..1991 returning 10,8
select year,sum(value) as total from stat where region="north" and
item="sales" group by year;
> Once you've created region I don't think you can delete it and all it's
> entries cos that would delete everything i.e. I believe you have to leave at
> least one value in the column whether it be null or north.
--
Kit
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
