Query Help -- Groups, joins, and calculations 1

[denoxis]

Hi,

I've been trying to do some calculations using three tables:

tbl_order
---------
order_id
order_ref -------------+
order_date |
|
|
tbl_visitors |
------------ |
visitor_id |
visitor_ref ------------+
visitor_date |
|
tbl_ref |
------- |
ref ------------+
keyword


What I want to do is, I want to see number of sales, number of visitors between the dates specified grouped by REF and KEYWORD.

Following query and the queries similar to this always return the number of visitors (count(visitor_id)) and number of sales (count(order_id)) the same, which is not possible. What type of query should I use for this kind of cases? Outer join didn't help BTW.

Thank you very much.


SELECT ref, keyword, COUNT(visitor_id), COUNT(order_id)
FROM [tbl_ref]
INNER JOIN tbl_orders ON [tbl_ref].ref = tbl_orders.order_ref
INNER JOIN tbl_visitors ON [tbl_ref].ref = tbl_visitors.visitor_ref
WHERE (tbl_visitors.visitor_date BETWEEN '9/10/02' AND '9/20/02') AND (tbl_orders.order_date BETWEEN '9/10/02' AND '9/20/02')
GROUP BY [tbl_ref].ref, [tbl_ref].keyword

 

[r937]

i'm guessing, but i bet your outer joins didn't work because the conditions were in the WHERE clause

try this --

SELECT ref
     , keyword
     , COUNT(visitor_id)
     , COUNT(order_id)
  FROM tbl_ref 
LEFT OUTER
  JOIN tbl_orders 
    ON tbl_ref.ref = tbl_orders.order_ref 
   AND tbl_orders.order_date 
       BETWEEN '9/10/02' AND '9/20/02'
LEFT OUTER
  JOIN tbl_visitors 
    ON tbl_ref.ref = tbl_visitors.visitor_ref
   AND tbl_visitors.visitor_date 
       BETWEEN '9/10/02' AND '9/20/02'
GROUP
    BY tbl_ref.ref
     , tbl_ref.keyword

rudy

http://rudy.ca/

 

[malexanian]

The visitor and order calculations are separate and must be done separately. Use subqueries:

SELECT
r.ref,
r.keyword,
( SELECT COUNT(*) FROM tbl_visitors
WHERE visitor_ref = r.ref AND
visitor_date >= '9/10/2002' AND
visitor_date < '9/21/2002' ) visitor_count,
( SELECT COUNT(*) FROM tbl_orders
WHERE order_ref = r.ref AND
order_date >= '9/10/2002' AND
order_date < '9/21/2002' ) order_count

This assumes that ref is the primary key of tbl_ref.

 

[denoxis]

r937, if there is a sale, your query returns a sale number equal to visitor number. Better than mine.

malexanian, your query seems work right.

Thank you very much both.

One more thing is, I can run the query in Enterprise Manager. However in Access with an ODBC connection, I don't see anything not even a error message nor a query result window.