there are the 2 tables.

tbl_1
id  animal   fruit
1  "monkey" "apple"
2  "bear" "grape"
3  "rabit" "orange"
4  "cat" "banana"

tbl_2
id  xxxx   number
1  "banana" 5
2  "orange" 3
3  "monkey" 10
4  "grape" 7
5  "bear" 6
6  "apple" 8
7  "cat" 9
8  "rabit" 6


i want to make the table as tbl_result below using any query statement.

tbl_result
animal_sum   fruit_sum
    31           23




*** Sent via Developersdex http://www.developersdex.com ***

Homework assignment perhaps?  In that case, try:

SELECT 31 AS animal_sum, 23 AS fruit_sum
INTO tbl_result

Show quoteHide quote

select sum(t2.number) as animal_sum, sum(t2.id) as fruit_sum
from tbl_1 t1 inner join tbl_2 t2 on t1.animal = t2.xxx
into tbl_result

Show quoteHide quote

Please learn how to design a schema. There is no such thing as
universal magical "id" in RDBMS.

CREATE TABLE AnimalsFruits
(animal_name CHAR(10) NOT NULL,
fruit_name CHAR(10) NOT NULL,
PRIMARY KEY (animal_name,fruit_name));

This next table is completely wrong!! It is full of mixed entities and
violates first normal form (1NF). A table models one and only one kind
of entity, not a fruit salad of animals and fruits.  In the South, we
use the term "Thingie Bobs" when we have no idea what the hell we are
dealing with.

CREATE TABLE ThingieBobs
(animal_or_fruit_name CHAR(10) NOT NULL PRIMARY KEY, -- look at that
name!!
entity_count INTEGER NOT NULL);

Now let's try a query:

SELECT SUM(CASE WHEN T.animal_name = AF.animal_or_fruit_name
                THEN entity_count
                ELSE 0 END) AS animal_tally,
       SUM(CASE WHEN T.fruit_name = AF.animal_or_fruit_name
                THEN entity_count
                ELSE 0 END) AS animal_tally
  FROM ThingBobs AS T,
       AnimalsFruits AS AF;

Mr. Celko, Just for the record, I think your query should read :

SELECT SUM(CASE WHEN AF.animal_name = T.animal_or_fruit_name
                THEN T.entity_count
                ELSE 0 END) AS animal_tally,
       SUM(CASE WHEN AF.fruit_name = T.animal_or_fruit_name
                THEN T.entity_count
                ELSE 0 END) AS animal_tally
  FROM ThingieBobs T, AnimalsFruits AF


But that is not my main point. 

1) I am wondering whether the composite primary key in your table
AnimalFruits should be a surrogate primary key. Usama Munir Dar  from 
www.asktom.oracle.com  writes "As I said -- if the composite primary key is
not a foreign key in lots of tables -- go for the composite primary key.  If
it is -- give serious consideration to the surrogate key populated via a
sequence (and don't even consider that "gaps" might be there -- that is not
even a little bit relevant, it is just a unique id)". I guess that if if the
composite primary key is a foreign key in lots of tables, certain operations
could get expensive.
2), I think your table definition does not address the possiblity that
fruit_name might be null for an animal type(suppose we have an animal that
does not eat any fruits)

In that case, a "column does not allow nulls" error when you tried to insert
a row

INSERT INTO AnimalsFruits VALUES('gorilla', NULL)

Thank you.




Show quoteHide quote

I saw the name celko after mine and two drops of pee came out.

Show quoteHide quote

At my age, that happens a lot to me.

Tom, Please try this query . It runs okay for me and may solve your problem.
You are probably disguising a proprietary problem with another metaphor. I
hope your question generates some interesting discussions:


select sum(a1.animal_sum) as "animal_sum", sum(a1.fruit_sum) as "fruit_sum"
from
(
    select sum(TBL_2.number) as "animal_sum", 0 as "fruit_sum"
    from TBL_2
    join TBL_1 on TBL_2.xxxx = TBL_1.animal
    UNION
    select 0 as "animal_sum" , sum(TBL_2.number) as "fruit_sum"
    from TBL_2
    join TBL_1 on TBL_2.xxxx = TBL_1.fruit
) a1

Thank you.



Show quoteHide quote

Triggers - run for multiple updated
parsing varchar fields
INSTEAD OF INSERT trigger
Why does update run differently standalone vs in procedure?
SELECT different columns based on the value of another column