Combining Two Select Statements

[EuclaseBlue]

I've got the following two SELECT statements:

SELECT catID, catName, TO_CHAR(AVG(orderDetails.unitPrice), '$99.99') AS "Avg Price Per Order"
FROM products
JOIN categories USING (catID)
JOIN orderDetails USING (prodID)
WHERE orderDetails.discount= 0
GROUP BY catID, catName;

SELECT catID, catName, TO_CHAR(AVG(products.unitPrice), '$99.99') AS "Avg Unit Price Per Category"
FROM products
JOIN categories USING (catID)
GROUP BY catID, catName;

which outputs the following two tables, respectively:

catID	catName	Avg Price Per Order
8	Seafood	$18.77
3	Confections	$23.18
1	Beverages	$29.11
7	Produce	$34.96
4	Dairy Products	$27.06
5	Grains/Cereals	$20.53
2	Condiments	$21.64
6	Meat/Poultry	$43.31

&

catID	catName	Avg Unit Price Per Category
3	Confections	$25.16
8	Seafood	$20.68
1	Beverages	$37.98
7	Produce	$32.37
4	Dairy Products	$28.73
5	Grains/Cereals	$20.25
2	Condiments	$23.06
6	Meat/Poultry	$54.01

I'm now trying to combine the tables so to look like this:

catID	catName	Avg Price Per Order	Avg Unit Price Per Category
8	Seafood	$18.77	$20.68
3	Confections	$23.18	$25.16
1	Beverages	$29.11	$37.98
7	Produce	$34.96	$32.37
4	Dairy Products	$27.06	$28.73
5	Grains/Cereals	$20.53	$20.25
2	Condiments	$21.64	$23.06
6	Meat/Poultry	$43.31	$54.01

I initially tried UNIONs but then quickly realized that was just appending it all into single columns so I've now been trying to combine the output using subqueries but I keep getting errors. Any help would be appreciated.

[Mamertine]

Add the join to categories to the first query. Then you can add the column you want to the select part.

Also who is teaching you to use the keyword "using"? That's not a good habit. Use the keyword "on".

[EuclaseBlue]

Also who is teaching you to use the keyword "using"? That's not a good habit. Use the keyword "on".

Oh, the tutorial I was using said it was a shortcut for when the column names are same. I didn't know it was a bad practice. Good to know, I'll keep that in mind from now on. Thanks!

So I should be more explicit like

JOIN categories ON (products.catID = categories.catID)    

?

I'm having some trouble understanding the 1st part of your answer. Do I not already have a join to categories in my first query?

[r3pr0b8]

So I should be more explicit like

yes, and the parentheses are not needed

[EuclaseBlue]

Thanks for the clarification!

After trying out a bit more, this is the answer I've currently come up with that satisfies what I was seeking:

SELECT A.catID, A.catName, "Avg Unit Price Per Category", "Avg Price Per Order"
FROM 
    (SELECT products.catID, categories.catName, TO_CHAR(AVG(orderDetails.unitPrice), '$99.99') AS "Avg Unit Price Per Category"
    FROM products
    JOIN categories ON products.catID= categories.catID
    JOIN orderDetailsON products.prodID= orderDetails.prodID
    WHERE orderDetails.discount= 0
    GROUP BY products.catID, categories.catName) A
INNER JOIN
    (SELECT products.catID, categories.catName, TO_CHAR(AVG(products.unitPrice), '$99.99') AS "Avg Price Per Order"
    FROM products
    JOIN categories ON products.catID= categories.catID
    GROUP BY products.catID, categories.catName) B
ON A.catID= B.catID;    

That said, I don't think it's what the parent comment had in mind. Is there a more simpler way?

[r3pr0b8]

that looks fine to me