Advanced Query

I have two table which i want to query.

TableA (aKey, aID, aBool, aNum)
TableB (bKey, bID, bNum)

i want the query to return 4 columns (ID, Num1, Num2, Num3)
where...
ID is some identification string
Num1 is the sum of TableA.aNum when TableA.aBool is true,
Num2 is the sum of TableA.aNum when TableA.aBool is false,
Num3 is the sum of TableB.bNum

I have tried

[code]
SELECT TableB.bID,
SUM(TableA1.aNum) As Total1,
SUM(TableA2.aNum) As Total2,
SUM(TableB.bNum) As Total3
FROM TableA As TableA1, TableA As TableA2, TableB
WHERE TableA1.aBool AND
NOT TableA2.aBool AND
TableA1.aID = TableA2.aID AND
TableB.bID = TableA1.aID
GROUP BY TableB.bID
[/code]

Any help apreciated.

Comments

  • [b][red]This message was edited by the JamesFos at 2002-2-24 12:2:59[/red][/b][hr]
    Is it possible to join the following tables ...
    [code]
    --------TableA--------
    A B C
    -------|------|-------
    just some random
    data to fill
    all these gaps
    [/code]

    [code]
    --------TableB--------
    D E F
    -------|------|-------
    more data to
    fill this table
    [/code]

    ... in the following manner ...

    [code]
    ---------------------------------------------
    A B C D E F
    -------|------|-------|-------|------|-------
    just some random NULL NULL NULL
    data to fill NULL NULL NULL
    all these gaps NULL NULL NULL
    NULL NULL NULL more data to
    NULL NULL NULL fill this table
    [/code]
    ...?

    This might solve the problem.





  • I currently don't have access to a DB, but it sounds like you need something like what is below. The only thing I am unsure of is if / how you specify that aBool = true or if you have to specify aBool = 1/0 to get the relationship you want. Depending on the database vendor and their SQL compliance you may have to change true/false with 1/0. I also make the assumption that the the tables will be connected by their respective keys.

    select
    sum( CASE WHEN TableA.aBool = true THEN TableA.anum ELSE 0 ) as NUM1,
    sum( CASE WHEN TableA.aBool = false THEN TableA.anum ELSE 0 ) as NUM2,
    sum( TableB.bnum ) as NUM3
    from TableA, TableB
    where TableA.AKey = TableB.Bkey;

    : I have two table which i want to query.
    :
    : TableA (aKey, aID, aBool, aNum)
    : TableB (bKey, bID, bNum)
    :
    : i want the query to return 4 columns (ID, Num1, Num2, Num3)
    : where...
    : ID is some identification string
    : Num1 is the sum of TableA.aNum when TableA.aBool is true,
    : Num2 is the sum of TableA.aNum when TableA.aBool is false,
    : Num3 is the sum of TableB.bNum
    :
    : I have tried
    :
    : [code]
    : SELECT TableB.bID,
    : SUM(TableA1.aNum) As Total1,
    : SUM(TableA2.aNum) As Total2,
    : SUM(TableB.bNum) As Total3
    : FROM TableA As TableA1, TableA As TableA2, TableB
    : WHERE TableA1.aBool AND
    : NOT TableA2.aBool AND
    : TableA1.aID = TableA2.aID AND
    : TableB.bID = TableA1.aID
    : GROUP BY TableB.bID
    : [/code]
    :
    : Any help apreciated.
    :

Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories