Re: Connection two select from where blocks

From: Martin Haltmayer <Martin.Haltmayer_at_0800-einwahl.de>
Date: Mon, 21 Feb 2000 13:19:00 +0200
Message-ID: <38B11F24.2D0712D3@0800-einwahl.de>

For the grand totals it should look like:

select v1.onhand, v2.rented, v3.ontime, v4.late, v5.income, v6.owed from
(SELECT COUNT(C.DVD_CODE)-COUNT(R.DVD_CODE) AS ONHAND FROM DVD_COPY C, DVD_RENTAL R
WHERE R.RENTAL_DATE IS NOT NULL
AND R.RETURN_DATE IS NULL) v1
, (SELECT COUNT(R.DVD_CODE) AS RENTED
FROM DVD_RENTAL R
WHERE R.RENTAL_DATE IS NOT NULL
AND R.RETURN_DATE IS NULL) v2
, (SELECT COUNT(R.DVD_CODE) AS ONTIME
FROM DVD_RENTAL R
WHERE R.RETURN_DATE = SYSDATE
AND (R.RETURN_DATE - R.RENTAL_DATE) < 2) v3 , (SELECT COUNT(R.DVD_CODE) AS LATE
FROM DVD_RENTAL R
WHERE (R.RENTAL_DATE + 2) >= SYSDATE
AND R.RETURN_DATE IS NULL) v4
, (SELECT SUM(I.PAYMENT) AS INCOME
FROM INVOICES I
WHERE I.TRANSACTION_DATE = SYSDATE) v5
, (SELECT (SUM(TRUNC(SYSDATE-R.RENTAL_DATE))* F.LATE_FEE) AS OWED FROM DVD_RENTAL R, DVD_COPY C, DVD D, DVD_FEE_CATEGORY F WHERE R.DVD_CODE=C.DVD_CODE

AND C.DVD_ID = D.DVD_ID
AND D.FEE_CATEGORY = F.FEE_CATEGORY
AND R.RETURN_DATE IS NULL

GROUP BY F.LATE_FEE) v6;

But I do not see the reason of the last group by as you do not select the grouped f.late_fee. So you will get for each fee one line with exactly the same grand totals for onhand, rented, ontime, late and income.

If you want it for each fee grouped by that fee I can offer you a consulting contract because you have to reveal your table structure and purpose of this query.

Martin

Spotly wrote:
>
> I'm trying to get the following fields:
> ONHAND, RENTED, ONTIME, OWED...and so on from the following. I know that the
> UNION isnt correct, but I cant seem to figure out how to do this. If someone
> could point me in the general direct I would appreciate it. Thanks for any
> help. Tim H.
>
> SELECT COUNT(C.DVD_CODE)-COUNT(R.DVD_CODE) AS ONHAND
> FROM DVD_COPY C, DVD_RENTAL R
> WHERE R.RENTAL_DATE IS NOT NULL
> AND R.RETURN_DATE IS NULL
> UNION
> SELECT COUNT(R.DVD_CODE) AS RENTED
> FROM DVD_RENTAL R
> WHERE R.RENTAL_DATE IS NOT NULL
> AND R.RETURN_DATE IS NULL
> UNION
> SELECT COUNT(R.DVD_CODE) AS ONTIME
> FROM DVD_RENTAL R
> WHERE R.RETURN_DATE = SYSDATE
> AND (R.RETURN_DATE - R.RENTAL_DATE) < 2
> UNION
> SELECT COUNT(R.DVD_CODE) AS LATE
> FROM DVD_RENTAL R
> WHERE (R.RENTAL_DATE + 2) >= SYSDATE
> AND R.RETURN_DATE IS NULL
> UNION
> SELECT SUM(I.PAYMENT) AS INCOME
> FROM INVOICES I
> WHERE I.TRANSACTION_DATE = SYSDATE
> UNION
> SELECT (SUM(TRUNC(SYSDATE-R.RENTAL_DATE))* F.LATE_FEE) AS OWED
> FROM DVD_RENTAL R, DVD_COPY C, DVD D, DVD_FEE_CATEGORY F
> WHERE R.DVD_CODE=C.DVD_CODE
> AND C.DVD_ID = D.DVD_ID
> AND D.FEE_CATEGORY = F.FEE_CATEGORY
> AND R.RETURN_DATE IS NULL
> GROUP BY F.LATE_FEE;
Received on Mon Feb 21 2000 - 05:19:00 CST