Re: Q: Queries combinig...

From: Matthias Gresz <GreMa_at_t-online.de>
Date: 1998/12/30
Message-ID: <368A00F5.89499405@Privat.Post.DE>#1/1

evlap_at_usa.net schrieb:
>
> Hi All!
> There are two queries
> 1) SELECT SUM (Field1) FROM Otch WHERE TRUNC (AuctionDate, 'dd')=TO_DATE
> ('24/12/98', 'dd/mm/yy');
> its result is stored into variable FullAmount
> 2) SELECT Field1, Field*100/:FullAmount "Percent" FROM Otch
> WHERE TRUNC (AuctionDate, 'dd')=TO_DATE ('24/12/98', 'dd/mm/yy');
> i.e. % of full amount is calculated in the second column.
> Q: is it possible to combine these two queris into one (I want to avoid
> using 2 queries)? It's nessary for me because I wanna insert this query
> in my Excel table.
>
> Thanks in advance.
>
> Regards, Eugene.

Does this do?

SELECT

	O.Field1, 
	O.Field1*100/S.FullAmount "Percent" 
FROM 
	Otch O,
	(SELECT 
		TRUNC(ACTIONDATE) AD,	-- leave this Line if you include the where
clause in S
		SUM (Field1) FullAmount
	FROM 
		Otch 
	WHERE 
		TRUNC (S.AuctionDate, 'dd')= TO_DATE('24/12/98', 'dd/mm/yy')
	GROUP BY
		TRUNC(ACTIONDATE)	-- leave this Line if you include the where clause
in S
	) S
WHERE 
	TRUNC (O.AuctionDate, 'dd')= TO_DATE('24/12/98', 'dd/mm/yy')
AND
	TRUNC (O.AuctionDate)=S.AD	-- leave this line if you include the where

clause in S
;

HTH
Matthias

-- 
Matthias.Gresz_at_Privat.Post.DE

Always log on the bright side of life.
http://www.stone-dead.asn.au/movies/life-of-brian/brian-31.htm

Received on Wed Dec 30 1998 - 00:00:00 CST