[DB-SIG] Help on this query

[Andy Todd]

Chris Cogdon wrote:
> On Tuesday 04 June 2002 18:57, Andy Todd wrote:
> 
> 
>>Titu Kim wrote:
>>
>>>Hi there,
>>>    I have two unrelated tables A and B which store
>>>different data. Let say the followings are the table
>>>schemas,
>>>
>>>Table A
>>>ID  number,
>>>Date number,
>>>EarnA  number
>>>
>>>Table B
>>>ID  number,
>>>Date number,
>>>EarnB number
>>>
>>>ID in Table A and Table B is the same data. Assume
>>>Date in Table A and Table B is in number format. I can
>>>get EarnA from Table A with this query
>>>
>>>Select sum(EarnA) from TableA
>>>where ID=XX and Date between mmm and nnn;
>>>
>>>Similar for TableB i can get EarnB
>>>Select sum(EarnA) from TableB
>>>where ID=XX and Date between mmm and nnn;
>>>
>>>The number of rows in TableA and TableB are different
>>>and EarnA and EarnB are two different data. Now if i
>>>want to show EarnA and EarnB in one query for the same
>>>ID in both Tables, how can i do this? Table schemeas
>>>are simplified for discussion purposes.
>>>
>>>Thanks.
>>>
>>>
>>>__________________________________________________
>>>Do You Yahoo!?
>>>Yahoo! - Official partner of 2002 FIFA World Cup
>>>http://fifaworldcup.yahoo.com
>>>
>>>
>>>_______________________________________________
>>>DB-SIG maillist  -  DB-SIG@python.org
>>>http://mail.python.org/mailman/listinfo/db-sig
>>
>>Try the following SQL statement;
>>
>>SELECT sum(A.EarnA), sum(B.EarnB)
>>FROM   TableA A, TableB B
>>WHERE  B.id = A.id
>>
>>This is bog standard SQL so I'd suggest you also take a look at a
>>tutorial
>>(http://directory.google.com/Top/Computers/Programming/Languages/SQL/FAQs,_
>>Help,_and_Tutorials/?tc=1)
>>
>>which may provide answers your questions more quickly than this mailing
>>list ;-)
> 
> 
> I don't believe that query will do what he was intending, since it creates a 
> cross-product over two tables, generating many MANY more rows than was 
> intended, inflating the 'earn' values or even decreasing them if the ID in 
> one table is not reflected in the other table.
> 
> The right solution will depend on what you're trying to do. If you want a 
> single combined earn value for the summation from both tables, you can create 
> a 'union' table of all the values, and then sum that. The following example 
> implies your table supports subselects:
> 
> select sum(earn) from ( select earna as earn from tablea where id=xx and 
> date>yy and date<zz union all select earnb as earn from tableb where id=xx 
> and date>yy and date<zz ) as joined;
> 
> However, if you want to keep the earn values from one table separate from the 
> earn values from the other, you need a different query:
> 
> select sum(earna), sum(earnb) from ( select earna, null as earnb from tablea 
> where id=xx and date>yy and date <<zz union all select null as earna, earnb 
> from tableb where id=2 and date>yy and date<zz ) as joined;
> 
> In both cases, you can replace 'joined' with any name. These tested to work 
> under PostgreSQL. MySQL doesn't support subselects, so you'll have to make do 
> with either temporary tables, or two queries.
> 

Chris,

You are absolutely right, I am an idiot. Mind you, thats some pretty 
scary SQL you have there and, as you say, it won't work in every 
database. I've been sitting here scratching my head trying to come up 
with some ANSI standard SQL that will do the job and failing terribly.

Until I thought "hang on, this is Python", so why not write two queries. 
I'll even put it in a function;

def getEarnings(cursor, id, lowDate, highDate):
     stmt = "SELECT sum(EarnA) FROM TableA WHERE id = %s AND date 
BETWEEN  '%s' AND '%s'" % ( id, lowDate, highDate)
     cursor.execute(stmt)
     earnA = cursor.fetchone()
     stmt = "SELECT sum(EarnB) FROM TableB WHERE id = %s AND date 
BETWEEN  '%s' AND '%s'" % ( id, lowDate, highDate)
     cursor.execute(stmt)
     earnB = cursor.fetchone()
     return (earnA, earnB)

Of course, if you want to add the two values together just make the last 
line read "return earnA+earnB".

If you like, you could make the formation of the value of stmt more 
dynamic, and stick the execute and fetch statements in a seperate 
function, but this illustrates my point I believe.

One final suggestion - don't ever call a column "date" it is usually a 
reserved word in the RDBMS.

Regards,
Andy
-- 
----------------------------------------------------------------------
 From the desk of Andrew J Todd esq - http://www.halfcooked.com