CODEWITHSUNDEEP
sqlsqlitejoin
Haseem hac
Haseem hac

Reputation: 31

why sqlite returns row counts of while Join

sqlite returns row count while executing this query 

SELECT sum(s.Card_10) ,sum(p.Card_10) FROM Sales_Table s ,  Purchase_Table p

ANSWER is sum(s.Card_10) 4 and sum(card_10) is 40

but if any execute these queries separately it reruns correct answer 

Select sum(Card_10) from sales_table

Answer 1 and 

Select sum(Card_10) from Purchase_table

Answer

40

why the error happen in such type of JOINS ?

Upvotes: 0

Views: 37

Answers (1)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

In the query 

SELECT sum(s.Card_10) ,sum(p.Card_10) FROM Sales_Table s ,  Purchase_Table p

a cross join of sales_table and purchase_table would be performed. So if sales_table has 1 row with card_10 column value of 1 and purchase_table has 4 rows with different values of card_10 that sum up to 40.

So the cross join (with some dummy data) would look like 

s.card_10   p.card_10
  1            5
  1            10
  1            8
  1            17

Hence you get the incorrect result.

One way of getting the correct counts in a single query is to use union.

select sum(Card_10) from sales_table 
union all
select sum(Card_10) from Purachase_table

or 

select max(fromsalestable) as fromsalestable, max(frompurchasetable) as frompurchasetable 
from
(
select sum(Card_10) as fromsalestable, null as frompurchasetable from sales_table 
union all
select null, sum(Card_10) from Purachase_table
) t

Upvotes: 1

Related Questions