Hi all,

Previous answer from Keith: forumpost:f75c5.

The value 82793601.0 is from a different table that I've now imported into SQLIte and I'd like to know how to calculate it within sqlite, but the two tables don't have the same schema. Some data is shared among it, but the column names are different.

I don't know what kind of join I need but I know this doesn't work.

select
	counts,
	xz_in,
	zy_out,
	(zy + PW) / coin_in * 100.0 as POP
from (
	select
	count(e.IDRecord) as counts,
	sum(e.wager) as xz_in,
	sum(e.zy) as zy_out,
	sum(cg.ClaimAmt) as PW
from
	table1 as e
join cg on
	e.count = cg.winRecordId
)

The result:

414919861,103729965250,93239178150,9700.0

Should actually be:

4342056,1085514000,976264100,94.0102679467976

(976264100 + 44230520) / 1085514000 * 100

Note: 82793601.0 was the value in the previous post, but there's now a new value and it's correct above, 976264100.

Do I want a cross join in this case?

Table1:

CREATE TABLE IF NOT EXISTS "table1" (
    number int,
    wager int,
    PAIC int

);

Table2:

CREATE TABLE IF NOT EXISTS "table2" (
    WinRecord int,
    ClaimAmt int
);

Some sample data for table1:

INSERT INTO table1(number,wager,PAIC) VALUES(1,1065,1);
INSERT INTO table1(number,wager,PAIC) VALUES(2,375,2);
INSERT INTO table1(number,wager,PAIC) VALUES(3,405,3);
INSERT INTO table1(number,wager,PAIC) VALUES(4,285,4);
INSERT INTO table1(number,wager,PAIC) VALUES(5,450,5);

Sample data for table2:

INSERT INTO table2(WinRecord,ClaimAmt) VALUES(1,32);
INSERT INTO table2(WinRecord,ClaimAmt) VALUES(2,32);
INSERT INTO table2(WinRecord,ClaimAmt) VALUES(3,32);
INSERT INTO table2(WinRecord,ClaimAmt) VALUES(4,40);
INSERT INTO table2(WinRecord,ClaimAmt) VALUES(5,40);

Your query:

select counts, 
      wager, 
       paic, 
       (paic + PW) / wager * 100.0 as POP 
  from ( 
          select count(e.number) as counts, 
                 sum(e.wager) as wager, 
                 sum(e.paic) as paic, 
                 sum(table2.ClaimAmt) as PW
            from table1
        group by counts
       ) as e
  join table2
    on e.counts = table2.winRecord

I guess my assumption about table alias' are wrong. I get no such column on e.number

This kind doesn't have an error but POP is wrong:

select counts, 
       counts, 
       wager, 
       (PAIC+ PW) / wager * 100.0 as POP 
  from ( 
          select count(number) as counts, 
                 sum(wager) as wager, 
                 sum(PAIC) as PAIC,
                 sum(table2.ClaimAmt) as PW 
from
	table1
left outer join table2
on number = table2.ClaimAmt
)