How to group data from two tables

[minli98]

Hi,

Say I have two tables, visitTbl which is used to keep track of each web visit (by users) and transactionTbl, which is used to keep track of each transaction made by users.

visitTbl:
VisitDate
UserId

transactionTbl:
TransactionDate
UserId
TransactionType


I'd like to create a report that pulls for a particular userid how many visits and how many transactions he makes each month. How can I write this query?

Thanks!

Regards,
Min

 

[r937]

SELECT UserId
     , YYYYMM
     , SUM(v) AS visits
     , SUM(t) AS transactions
  FROM ( SELECT UserId
              , CONVERT(CHAR(7),VisitDate,102) AS YYYYMM
              , COUNT(*) AS v
              , 0        AS t
           FROM visitTbl
         GROUP
             BY UserId 
       UNION ALL
         SELECT UserId
              , CONVERT(CHAR(7),TransactionDate,102) 
              , 0 
              , COUNT(*) 
           FROM transactionTbl
         GROUP
             BY UserId ) AS data
GROUP
    BY UserId
     , YYYYMM

r937.com | rudy.ca

 

[minli98]

Hi Rudy,

Thank you so much for your help. The code you wrote is what I what I was looking for. Thanks again.

Regards,
Min