SQLite Forum

What join is needed for non-similar data?
Login

What join is needed for non-similar data?

(1.1) By ThanksRyan on 2021-07-13 23:14:42 edited from 1.0 [link] [source]

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?

(2) By Keith Medcalf (kmedcalf) on 2021-07-13 00:27:17 in reply to 1.0 [source]

You probably want this:

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
        group by counts
       ) as e 
  join cg 
    on e.counts = cg.winRecordId
;

You need to close the subselect before joining to another table.

(3) By ThanksRyan on 2021-07-13 23:39:20 in reply to 2 [link] [source]

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
)