Re: SQL Query Help

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 Mon Jul 30 2001 - 15:30:55 CEST