How to display a column from a multiple tables in a SQL query?

Question

I am attempting to display a column from one of 3 tables I have in my query, but I can't seem to figure out how to get it to work properly. No matter what I try, it doesn't seem to work. It seems easy, but maybe I am missing something really simple. Here is what I have:

SELECT hpg.id_number,
     hpg.id_description,
     COUNT (hdd.number_of_games) games,
     SUM (NVL (price, 0)) total,
     SUM (number_of_food) food
--I tried adding column here, but nothing seemed to work
FROM (SELECT number_of_games, price, prints
        FROM (hopeful_dog_hops)
       WHERE status = 'Done') hdd,
     (SELECT SUM (number_of_food) number_of_food, number_of_games
          FROM hot_digity_dog
      GROUP BY number_of_games) hdd1,
     (SELECT id_description, sort_types, id_number
        FROM (hot_pick_games)
       WHERE disabled = 'TRUE' AND viewable_type = 'OK') hpg
WHERE hdd.prints(+) = hpg.id_number
     AND hdd.number_of_games = hdd1.number_of_games
GROUP BY hpg.id_description, hpg.id_number, sort_types
ORDER BY sort_types

This huge query displays these columns:

ID_NUMBER    ID_DESCRIPTION    GAMES    TOTAL    FOOD

I need to add one more column:

TEST

from the hot_digity_dog table.

Don't worry so much about the names of everything, but I just need to figure out how exactly I can get another column to display in the query. I tried adding TEST to the first SELECT statement, but it gave me an error saying: "TEST:invalid identifier."

I know the query works (without the added TEST part, so it can't be an error in the query). It has to be something with the new added part.

Any help would be greatly appreciated. Thanks in advance.

Here is the query with the new column:

SELECT hpg.id_number,
     hpg.id_description,
     COUNT (hdd.number_of_games) games,
     SUM (NVL (price, 0)) total,
     SUM (number_of_food) food,
     hdd.TEST
FROM (SELECT number_of_games, price, prints
        FROM (hopeful_dog_hops)
       WHERE status = 'Done') hdd,
     (SELECT SUM (number_of_food) number_of_food, number_of_games
          FROM hot_digity_dog
      GROUP BY number_of_games) hdd1,
     (SELECT id_description, sort_types, id_number
        FROM (hot_pick_games)
       WHERE disabled = 'TRUE' AND viewable_type = 'OK') hpg
WHERE hdd.prints(+) = hpg.id_number
     AND hdd.number_of_games = hdd1.number_of_games
GROUP BY hpg.id_description, hpg.id_number, sort_types
ORDER BY sort_types

And here is what the column headers should look like:

ID_NUMBER    ID_DESCRIPTION    GAMES    TOTAL    FOOD    TEST

Please show the query where you added the additional column. — Sam M
– Sam M, Commented Nov 25, 2017 at 20:33
You never select the TEST column in the hdd query. Select it there, along with number_of_games, price, prints and I suspect it will work. — Zorkolot
– Zorkolot, Commented Nov 25, 2017 at 20:43

Zorkolot · Accepted Answer · 2017-11-25 20:44:56Z

1

I think you are just missing the test column in the hdd query:

SELECT hpg.id_number,
     hpg.id_description,
     COUNT (hdd.number_of_games) games,
     SUM (NVL (price, 0)) total,
     SUM (number_of_food) food,
     hdd.TEST
FROM (SELECT number_of_games, price, prints, TEST
        FROM (hopeful_dog_hops)
       WHERE status = 'Done') hdd,
     (SELECT SUM (number_of_food) number_of_food, number_of_games
          FROM hot_digity_dog
      GROUP BY number_of_games) hdd1,
     (SELECT id_description, sort_types, id_number
        FROM (hot_pick_games)
       WHERE disabled = 'TRUE' AND viewable_type = 'OK') hpg
WHERE hdd.prints(+) = hpg.id_number
     AND hdd.number_of_games = hdd1.number_of_games
GROUP BY hpg.id_description, hpg.id_number, sort_types
ORDER BY sort_types

answered Nov 25, 2017 at 20:44

Zorkolot

2,0271 gold badge12 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user5683823 · Accepted Answer · 2017-11-25 20:40:53Z

The problem is that you are not selecting from hot_digity_dog in the outer query. You select from an AGGREGATE subquery based on that table - a subquery you aliased as hdd1. So, it is not clear how you want to add that column. Is test completely determined by number_of_games? If it is, then you can add test to the select and group by clauses of the aggregate subquery, and then you can select it in the outer query.

If test is not determined by number_of_games, do you want to sum number_of_food as grouped by number_of_games and test? If so, again, add test to select and group by in the subquery (but the results will be different from grouping by number_of_games alone).

If test is not determined by number_of_games, you want to group by number_of_games for the sum, but you want to show test anyway, you may want to use analytic sum() rather than aggregate sum().

This is only a small example to prove to you that what you posted is not nearly enough to allow us to help you. Please provide (much) more detail - by editing the original question, NOT in Comments.

Collectives™ on Stack Overflow

How to display a column from a multiple tables in a SQL query?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related