Re: SQL Query Help

From: crappy <crappygolucky_at_hotmail.com>
Date: 31 Jul 2001 09:15:40 -0700
Message-ID: <ce31c410.0107310815.4c31f3a0_at_posting.google.com>


ya, so what's wrong with group by in views? an inline view, even: something like

select day, domain, sum(requests), sum(redeems),

   sum(redeems)/sum(requests) ratio from (
SELECT trunc(date_created) day, domain, count(offer_id) requests, 0 redeems FROM
offer_tracking GROUP BY trunc(date_created), domain union
SELECT trunc(date_created) day, domain, 0, count(offer_id) redeems FROM
offer_redeem GROUP BY trunc(date_created), domain )
group by day, domain

(and use some decode nonsense or something to take care of divide by zero, if necessary)

or am i missing something ..

"Tony Hunt" <tonster_at_bigpond.net.au> wrote in message news:<jkd97.74774$Xr6.344817_at_news-server.bigpond.net.au>...
> Mate,
>
> You've got me too! I started thinking about subqueries with unions...
> otherwise creating views or tables with the two SELECT functions (ie if you
> can use GROUP BY in a CREATE TABLE/VIEW statement...)
>
> Depending on how this is to be presented, I would consider using a reporting
> tool such as MS Access or Crystal Reports??...
>
> Tony
>
> "Shaun Martinec" <smartinec_at_usa.net> wrote in message
> news:c471783e.0107221608.798ba144_at_posting.google.com...
>
> > I have 2 queries that I would like to join together based on date and
> > a domain field. However, sometimes a date will apear in one table, but
> > not the other. If there is a date in either result I want the row to
> > still show up. It is better explained with the actual queries.
> >
> > The first query:
> > SELECT trunc(date_created) day, domain, count(offer_id) requests FROM
> > offer_tracking GROUP BY trunc(date_created), domain
> >
> > First Query Example Result:
> > DAY DOMAIN REQUESTS
> > --------- -------------------------------- ----------
> > 03-JUL-01 SHAUNM 2
> > 03-JUL-01 TEST 4
> > 05-JUL-01 SHAUNM 1
> > 05-JUL-01 TEST 2
> > 11-JUL-01 TEST 1
> > 13-JUL-01 TEST 2
> > 19-JUL-01 TEST 8
> > 20-JUL-01 TEST 6
> >
> > The second Query:
> > SELECT trunc(date_created) day, domain, count(offer_id) redeems FROM
> > offer_redeem GROUP BY trunc(date_created), domain
> >
> > Second Query Example Result:
> > DAY DOMAIN REDEEMS
> > --------- -------------------------------- ----------
> > 03-JUL-01 TEST 2
> > 09-JUL-01 TEST 1
> > 19-JUL-01 TEST 2
> > 20-JUL-01 TEST 1
> >
> > Desired Combined Result:
> > DAY DOMAIN REQUESTS REDEEMS RATIO
> > --------- -------------------------------- ---------- ---------- -----
> > 03-JUL-01 SHAUNM 2 0 0
> > 03-JUL-01 TEST 4 2 0.5
> > 05-JUL-01 SHAUNM 1 0 0
> > 05-JUL-01 TEST 1 0 0
> > ...and so on
> >
> > Any help in writing a query that accomplishes the desired result would
> > be great. I've looked at intersection and union, but I can't seem to
> > get the correct result. Thanks in advance.
> >
> > -Shaun
Received on Tue Jul 31 2001 - 18:15:40 CEST

Original text of this message