Question Oracle Subquery help

[Bj]

So I am supposed to help my classmates if they get stuck on a report, but I kind-of got stuck myself.

So the report is;

# Report 6 - A summary of sales for any given day, categorized by Package type. (i.e., the total sales written on the specified day for each package type subscribed to by customers, not bills paid). This report should give details about each package type, the package price, the number of packages sold, and then the total sales amount for that package. Once again, remember that this is going to be a date driven report.

and the necessary entities are;

CREATE TABLE PACKAGE 
(
PACK_ID NUMBER(5) NOT NULL,
PACK_DESC VARCHAR(64),
PACK_PRICE NUMBER(6,2) NOT NULL,
PACK_INC_DOC NUMBER(1),
PACK_INC_DOC2 NUMBER(1),
PACK_INC_LIFE NUMBER(1),
PACK_INC_LIFE2 NUMBER(1),
PACK_INC_MOVIE NUMBER(1),
PACK_INC_MOVIE2 NUMBER(1),
PACK_INC_NEWS NUMBER(1),
PACK_INC_NEWS2 NUMBER(1),
PACK_INC_SPORT NUMBER(1),
PACK_INC_SPORT2 NUMBER(1),
CONSTRAINT PACKAGE_PACK_ID_pk PRIMARY KEY (PACK_ID)
);

CREATE TABLE CUSTOMER
(
CUST_ID        NUMBER(5) NOT NULL,
CUST_FNAME    CHAR(32) NOT NULL,
CUST_LNAME    CHAR(32) NOT NULL,
CUST_ADDRESS    VARCHAR(64) NOT NULL,
CUST_ACC    NUMBER(5) NOT NULL,
CUST_PHONE    CHAR(10),
CUST_START    DATE NOT NULL,
CUST_END    DATE,
CUST_TERM    NUMBER(1) NOT NULL,
CUST_TERM_RES    VARCHAR(64),
CUST_KILLER_ID    NUMBER(5),
PACK_CUST    NUMBER(5) NOT NULL,
CONSTRAINT CUSTOMER_CUST_ID_pk PRIMARY KEY (CUST_ID),
CONSTRAINT CUSTOMER_PACK_CUST_fk FOREIGN KEY (PACK_CUST) REFERENCES PACKAGE(PACK_ID)
);

So I am able to get the number of packages sold like;

     SELECT pack_cust AS “Package ID”, count(CUST_ID) AS “Number of Packages Sold”
     FROM CUSTOMER
     GROUP BY pack_cust
     ORDER BY pack_cust;

...but then I am not sure on where to go from there. My book doesnot describe select subqueries that are inside select clauses. It only shows the where or having clauses. How can I take a multi-column result from a query and put it into the select clause, and better yet match it with the package ID?

 

[TJohns]

Ummm...where's your SALES tables to track *WHO* bought *HOW MANY* of *WHAT* *WHEN* ???

From the wording of the report requirements, this seems to be the whole point of this project - use the SALES table as a many-to-many link between CUSTOMERS and PACKAGES

Trevor

 

[Bj]

Ummm...where's your SALES tables to track *WHO* bought *HOW MANY* of *WHAT* *WHEN* ???

From the wording of the report requirements, this seems to be the whole point of this project - use the SALES table as a many-to-many link between CUSTOMERS and PACKAGES

Trevor

There is no sales table;

who = (customer)
how many = 1 package
what = customer.pack_cust
when = customer.CUST_START

Though yes it might be easier with a sales table, I think I will just resort to asking the professor. He did after all approve the design.

and if it needs a sales table, well its a little late now. After this class I can guarantee database design are not for me.

---------- Post added 02-21-10 at 05:51 PM ---------- Previous post was 02-20-10 at 10:32 PM ----------

oh

Guess what? There is a sales table. I was looking through the drop statements and saw drop table sales... so I just added a few attributes.


Still this is probably a really stupid way of doing it, but

UPDATE sales
SET total_cust = 0;

UPDATE sales
SET total_income = 0;

SELECT PACK_ID, total_cust, total_income FROM sales;

UPDATE sales
SET total_cust = 
(TOTAL_CUST + (SELECT COUNT(pack_cust) FROM customer WHERE PACK_CUST = 10001))
WHERE PACK_ID = 10001;

UPDATE sales
SET total_cust = 
(TOTAL_CUST + (SELECT COUNT(pack_cust) FROM customer WHERE PACK_CUST = 10002))
WHERE PACK_ID = 10002;

UPDATE sales
SET total_cust = 
(TOTAL_CUST + (SELECT COUNT(pack_cust) FROM customer WHERE PACK_CUST = 10003))
WHERE PACK_ID = 10003;

UPDATE sales
SET total_cust = 
(TOTAL_CUST + (SELECT COUNT(pack_cust) FROM customer WHERE PACK_CUST = 10004))
WHERE PACK_ID = 10004;


UPDATE sales
SET total_income = 
    (
    SELECT SUM(p.pack_price) 
    FROM package p, customer c
    WHERE c.pack_cust = p.pack_ID
    AND p.pack_id = 10001
    GROUP BY p.pack_id
    )
WHERE PACK_ID = 10001;

UPDATE sales
SET total_income = 
    (
    SELECT SUM(p.pack_price) 
    FROM package p, customer c
    WHERE c.pack_cust = p.pack_ID
    AND p.pack_id = 10002
    GROUP BY p.pack_id
    )
WHERE PACK_ID = 10002;

UPDATE sales
SET total_income = 
    (
    SELECT SUM(p.pack_price) 
    FROM package p, customer c
    WHERE c.pack_cust = p.pack_ID
    AND p.pack_id = 10003
    GROUP BY p.pack_id
    )
WHERE PACK_ID = 10003;

UPDATE sales
SET total_income = 
    (
    SELECT SUM(p.pack_price) 
    FROM package p, customer c
    WHERE c.pack_cust = p.pack_ID
    AND p.pack_id = 10004
    GROUP BY p.pack_id
    )
WHERE PACK_ID = 10004;

--********************--
--***Select Queries***--
--********************--

SELECT p.PACK_ID, p.pack_desc, p.pack_price, total_cust, total_income 
FROM sales s, package p
WHERE s.pack_ID = p.pack_ID;

Cannot believe I didn't see that, maybe because it was 2AM at the time or something...