SQL Join Help

[iamthesgt]

In going through some homework, I was able to do some of the problems, but not others. For example, one problem was "Produce a list of employee last name, first name, job function, department name and department location. Use appropriate, user friendly column aliases." I finished this with this code:

SELECT EMPLOYEE.LAST_NAME "Last Name", EMPLOYEE.FIRST_NAME "First Name", JOB.FUNCTION "Job Function", DEPARTMENT.NAME "Department", LOCATION.REGIONAL_GROUP "Location"
FROM DEMO.EMPLOYEE, DEMO.JOB, DEMO.DEPARTMENT, DEMO.LOCATION
WHERE JOB.JOB_ID=EMPLOYEE.JOB_ID AND
EMPLOYEE.DEPARTMENT_ID=DEPARTMENT.DEPARTMENT_ID AND DEPARTMENT.LOCATION_ID=LOCATION.LOCATION_ID
ORDER BY EMPLOYEE.LAST_NAME

However, I have several similar problems that do not work. They all give me the same error: "ERROR at line 1: ORA-00937: not a single-group group function". For example, one problem states to "Produce a report that shows the description of the product and the sales price for the product with the highest unit sales price. Use appropriate, user friendly column aliases." I tried to solve this in a similar fashion, but got the above error. Here is what I did:

SELECT PRODUCT.DESCRIPTION "Product", (MAX(PRICE.LIST_PRICE)) "Item Price"
FROM DEMO.PRODUCT, DEMO.PRICE
WHERE PRICE.PRODUCT_ID=PRODUCT.PRODUCT_ID

Table PRODUCT has columns PRODUCT_ID, DESCRIPTION and table PRICE has columns PRODUCT_ID, LIST_PRICE, MIN_PRICE, START_DATE, END_DATE . The MAX function works on the column PRICE.LIST_PRICE by itself, but not when the tables are joined. I also can join the tables using

SELECT PRODUCT.DESCRIPTION "Product", PRICE.LIST_PRICE "Item Price"
FROM DEMO.PRODUCT, DEMO.PRICE
WHERE PRICE.PRODUCT_ID=PRODUCT.PRODUCT_ID
ORDER BY PRICE.LIST_PRICE DESC

, but I only need the top entry, not the entire list.
If anyone can see how I can fix this, that would be great. I have 2 other problems that I have exactly the same problem with that I haven't been able to figure out through my textbook or google. Thanks.

[hfx642]

You want to find the highest price for a particular product.
For EACH product, you have several prices, and your trying to find the highest.
You already have Max (Price.List_Price), but you want the highest price PER product.
So, you have to... GROUP BY Product.Description (in YOUR example!)

You also may want to add... ORDER BY Upper (Product.Description)

[iamthesgt]

So like this? It doesn't work, and I already tried putting the group by inside the statement. It gives me "ERROR at line 1: ORA-00907: missing right parenthesis", and I've tried putting the parentheses everywhere, but I get the same error. Could you help with where to put the group by statement? Thanks.

SELECT PRODUCT.DESCRIPTION, ((SUM(SALES.AMOUNT)) FROM DEMO.SALES GROUP BY PRODUCT.DESCRIPTION) "Total Sales"
FROM DEMO.PRODUCT
WHERE PRODUCT.PRODUCT_ID=SALES.PRODUCT_ID
ORDER BY (SUM(AMOUNT)) DESC

[hfx642]

Well, if I have to spell it out for you...

Select P.Description "Product", Sum (S.Amount) "Sales"
From Demo.Sales S, Demo.Product P
Where S.Product_ID = P.Product_ID
Group by P.Description
Order by Upper (P.Description);

Or your other query...

Select P1.Description "Product", Max (P2.List_Price) "Price"
From Demo.Price P2, Demo.Product P1
Where P2.Product_ID = P1.Product_ID
Group by P1.Description
Order by Upper (P1.Description);

[iamthesgt]

Thanks, but I had tried that. I need only the top row of the table. That gives me the sum of each unique product description. This is what it gives me:

DESCRIPTION	 Total Sales
SP TENNIS RACKET	 8424
WIFF SOFTBALL MITT (LH)	 7665
DUNK BASKETBALL OUTDOOR	 7590.8
SB ENERGY BAR-6 PACK	 7195.6
RH: "GUIDE TO BASKETBALL"	 669.8
ACE TENNIS BALLS-3 PACK	 6404.9
WIFF SOFTBALL BAT II	 6348
DUNK BASKETBALL PROFESSIONAL	 5973.5
RH: "GUIDE TO SOFTBALL"	 556.75
WIFF SOFTBALL, SMALL	 5551.1
WIFF SOFTBALL, LARGE	 5127
SP JUNIOR RACKET	 4950
...and more rows

[iamthesgt]

I did finally solve the problems and here is what I got:

Produce a list of employee last name, first name, job function, department name and department location. Use appropriate, user friendly column aliases.

SELECT EMPLOYEE.LAST_NAME "Last Name", EMPLOYEE.FIRST_NAME "First Name", JOB.FUNCTION "Job Function", DEPARTMENT.NAME "Department", LOCATION.REGIONAL_GROUP "Location"
FROM DEMO.EMPLOYEE, DEMO.JOB, DEMO.DEPARTMENT, DEMO.LOCATION
WHERE JOB.JOB_ID=EMPLOYEE.JOB_ID AND
EMPLOYEE.DEPARTMENT_ID=DEPARTMENT.DEPARTMENT_ID AND DEPARTMENT.LOCATION_ID=LOCATION.LOCATION_ID
ORDER BY EMPLOYEE.LAST_NAME

Produce a report that shows the description of the product and the sales price for the product with the highest unit sales price. Use appropriate, user friendly column aliases.

select distinct description "Product Description", actual_price AS "Sales Price"
   from demo.item i, demo.product p
   where i.product_id = p.product_id
   and actual_price = (select max(actual_price)
                       from demo.item);

Thanks for your help.