Re: Newbie -- db reporting help!

From: Martin Haltmayer <Martin.Haltmayer_at_0800-einwahl.de>
Date: Sat, 11 Dec 1999 19:27:46 +0200
Message-ID: <38528992.11008A95@0800-einwahl.de>

Hi Eric,

try this:

insert into customer (id, zip) values (123, 56789);
insert into customer (id, zip) values (924, 56789);
insert into customer (id, zip) values (345, 98760);

insert into orders (ord_id, cust_id, ord_amt, ord_date) values (44, 123, 12.34, sysdate);
insert into orders (ord_id, cust_id, ord_amt, ord_date) values (55, 123, 12.34, sysdate);

select

	a.zip
	, count (distinct o.ord_id)
from (
	select
		u.cust_id
		, u.ord_id
		, 1 as cnt
	from orders u
	where 1 = 1
	and     months_between(u.ord_date, sysdate) <= 6
	and     u.ord_amt between 5 and 49

) o, customer a
where o.cust_id (+) = a.id
group by a.zip
order by a.zip
;

Output:

56789                       2
98760                       0

I assume that is what you want.

Martin

Eric Raskin wrote:
>
> Help!!! I've got a master table like this:
>
> create table customer(
> id number,
> name varchar2(30),
> street varchar2(30),
> city varchar2(20),
> state varchar2(2),
> zip varchar2(5)
> );
>
> .. and a detail table like this:
>
> create table orders(
> ord_id number,
> cust_id number,
> ord_date date,
> ord_amt number(9,2)
> );
>
> I need to get statistical counts for a report. It would look something like
> this:
>
> ZIP Last 6 Mos Entire File Last 6 Mos $5-$49 Last
> 6 Mos $50-$99 Last 6 Mos $100+
> 11111 123
> 40 55 28
> 11112 78
> 22 37 19
>
> etc.
>
> I'm planning on using Oracle Reports for this. The only thing I can think
> of is to write 4 queries (one per column) that look like this:
>
> select a.zip, o.count(*)
> from orders o, address a
> where o.cust_id = a.id
> and months_between(o.ord_date, sysdate) <= 6
> and o.ord_amt between 5 and 49
> group by a.zip;
>
> This would generate the second column. The problem with this is
> coordinating the data in the rows. If a particular zip code has 0 in a
> column, it won't show up in the query at all. So, if I put the queries into
> 4 repeating columns down the page, the zips won't line up!!
>
> What's the correct way to do this? (Short of writing a report in COBOL
> using procob)....
>
> TIA
>
> Eric Raskin
> eraskin_at_paslists.com
>
> P.S. There's actually more to the problem than this. I've simplified it as
> much as I can in order to make it coherent. :-) The customers have type
> codes that have to broken out as well, which will become different matrix
> groups (I hope).
Received on Sat Dec 11 1999 - 11:27:46 CST