Add Column From Second Query SQL

Question

I am trying to concatenate the results of two of my queries into the same result table and I am not sure how to do this. I tried to play around with the UNION and JOIN operators, but could not figure it out. This is the SQL for the two queries that I want to concatenate. Both individual queries get the results that they are supposed to. Thanks in advance!

SELECT s.Store_Num || ': ' || s.Store_Name AS "Store",
        COUNT(e.Store_Num) AS "Total Rented"
FROM Employee e JOIN store s ON e.Store_Num = s.Store_Num
        JOIN rental r ON e.Emp_ID = r.Emp_ID
        JOIN rented_item ri ON r.Rental_Num = ri.Rental_Num
WHERE(SYSDATE - Rent_Date) < 60
GROUP BY s.Store_Num, s.Store_Name;
UNION
SELECT COUNT(i.Store_Num) AS "Total Inventory"
FROM inventory i JOIN store s ON i.Store_Num = s.Store_Num
GROUP BY s.Store_Num, s.Store_Name;

check that union querys must select the same number of values — Aramillo
– Aramillo, Commented Nov 13, 2014 at 12:55

Falco · Accepted Answer · 2014-11-13 13:06:56Z

6

Just pack multiple Sub-Selects into one statement!

You have two queries which count rows, so they should be written as two queries each with a COUNT(*) because you count rows, or you should be using a COUNT(item_num) because you count items and not stores, this will be clearer for the reader.

You then simply select all Stores and for each store you do the two counts in sub-queries - this is easy to maintain, and the optimizer should get the right join predicates.

SELECT s.Store_Num || ': ' || s.Store_Name "Store",
       ( SELECT COUNT(*)
           FROM Employee e
           JOIN rental r ON e.Emp_ID = r.Emp_ID
           JOIN rented_item ri ON r.Rental_Num = ri.Rental_Num
          WHERE e.Store_Num = s.Store_Num
            AND (SYSDATE - Rent_Date) < 60
       ) "Total Rented",
       ( SELECT COUNT(*)
           FROM inventory i WHERE i.Store_Num = s.Store_Num
       ) "Total Inventory"
  FROM store s
;

answered Nov 13, 2014 at 13:06

Falco

3,46627 silver badges29 bronze badges

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

4 Comments

Greek Freak Over a year ago

Do you by chance know how to divide the second and third column together? I am sure I will figure it out eventually but figured I would ask.

Falco Over a year ago

Divide together? Do you want to add their values? One column with rented + inventory ?

Greek Freak Over a year ago

I mean like rented/inventory. It tells me that inventory is an invalid identifier

Falco Over a year ago

You have to pack a big select around everything and do SELECT "Store", "Total Rented", "Total Inventory", "Total Rented"/"Total Inventory" FROM (...The whole Select Statement);

gmiley · Accepted Answer · 2014-11-13 13:01:17Z

2

Judging from your columns, a UNION is not what you want. A couple of different options would be to use a JOIN or sub query instead. I don't know how all of your data is set up, but this should be close.

SELECT s.Store_Num || ': ' || s.Store_Name AS "Store",
        COUNT(e.Store_Num) AS "Total Rented", (
          SELECT COUNT(i.Store_Num) 
          FROM inventory i 
          WHERE i.Store_Num = s.Store_Num) AS "Total Inventory"
FROM Employee e 
JOIN store s 
ON e.Store_Num = s.Store_Num
JOIN rental r ON e.Emp_ID = r.Emp_ID
JOIN rented_item ri ON r.Rental_Num = ri.Rental_Num
WHERE(SYSDATE - Rent_Date) < 60
GROUP BY s.Store_Num, s.Store_Name;

answered Nov 13, 2014 at 13:01

gmiley

6,6041 gold badge15 silver badges25 bronze badges

3 Comments

Aramillo Over a year ago

I think you're rigth +1

Falco Over a year ago

I would pack both queries into subqueries, with this mixtures is hard to distinguish how the subquery is related to the joins...

gmiley Over a year ago

Falco's answer may be a bit more of a cleaner looking solution compared to mine. While this one works, you might want to actually implement Falco's for readability sake. It should return the same results as mine, you can run some tests to see which one runs faster and make your decision on that.

Ankit Bajpai · Accepted Answer · 2014-11-13 12:57:03Z

0

Try this query:-

SELECT s.Store_Num || ': ' || s.Store_Name AS "Store", COUNT(e.Store_Num) AS "Total Rented",
       COUNT(i.Store_Num) AS "Total Inventory"
FROM Employee e JOIN store s ON e.Store_Num = s.Store_Num
JOIN rental r ON e.Emp_ID = r.Emp_ID
JOIN rented_item ri ON r.Rental_Num = ri.Rental_Num
JOIN inventory i ON i.Store_Num = s.Store_Num
WHERE(SYSDATE - Rent_Date) < 60
GROUP BY s.Store_Num, s.Store_Name;

This might help you.

answered Nov 13, 2014 at 12:57

Ankit Bajpai

13.6k4 gold badges27 silver badges43 bronze badges

2 Comments

Falco Over a year ago

I don't think this will work, because with additional joins your number of rows will explode! - example: 2 rentals and 2 items will result in 4 rows and thus each row is counted twice!

Greek Freak Over a year ago

Falco is correct the calculated counts are much too high when I enter this query. I have also tried to create two new tables in the FROM clause but that didn't seem to be getting closer to what I wanted

Collectives™ on Stack Overflow

Add Column From Second Query SQL

3 Answers 3

4 Comments

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related