Count and Join 1

[takeover]

Hi Gurus,

I have three tables like below

Table1

CUST_ID REC_NUM
100 00
100 01
100 02
101 00
101 02
-----------
-----------

Table2
CUST_ID REC_NUM DATE
100 00 10/10/2002
100 00 10/11/2002
100 00 10/12/2002
100 01 10/15/2002
100 01 10/16/2002
101 01 10/20/2002
101 01 10/21/2002
103 00 10/27/2002

Table3
CUST_ID REC_NUM DATE
100 00 11/10/2002
100 00 11/11/2002
100 00 11/12/2002
100 01 11/15/2002
100 01 11/16/2002
100 01 11/17/2002
101 01 11/20/2002
101 01 11/21/2002
101 01 11/22/2002
102 00 11/28/2002

I have two separate queries which gives me output with the counts of dates

CUST_ID REC_NUM COUNT1
100 00 3
100 01 2
101 01 2
103 00 1

CUST_ID REC_NUM COUNT2
100 00 3
100 01 3
101 01 3
102 00 1

How could I combine these two queries so that I could get an output like this?

CUST_ID REC_NUM COUNT1 COUNT2
100 00 3 3
100 01 2 3
101 01 2 3
102 00 1
103 00 1

Any help is really appreciated.

 

[JonFer]

Union the two queries and then write a crosstab query based on the union:

Select Cust_ID, REC_NUM, "Count1" as Type, Count1
Union All
Select Cust_ID, REC_NUM, "Count2" as Type, Count2


In the crosstab, use Count1 as your value (with Max or Min) and Type as the column heading.

 

[takeover]

Hi JonFer,

Thanks a lot! So nice.

takeover

 

[takeover]

Hi,

Can I have a total count column also in this query?

CUST_ID REC_NUM COUNT1 COUNT2 TOTAL_COUNT
100 00 3 3 6
100 01 2 3 5
101 01 2 3 5
102 00 0 1 1
103 00 1 0 1

takeover

 

[JonFer]

(Count1+Count2) as TotalCount ' Sum it and set it as a Row Heading

 

[takeover]

Hi JonFer,

Thanks!

takeover