Merging 2 columns from the same table

[bettygoh23]

Hi All,

This is what I'm trying to accomplish:

I have a table T1 with the following fields
- [UID]
- [OrigUID]
- [Type] (where type can be either B or C)

| UID | OrigUID | Type |
+-----+---------+------+
U1 | | B |
U1 | U1 | C |
U3 | U1 | C |
U3 | U2 | C |
U3 | U2 | C |

What I want to do is to get:
- the count of B in [Type] as [B count]for each distinct [UID]
- the count of C in [Type] as [C count]for each distinct [OrigUID]
- [All UID] where it combines the distinct [UID] and distinct [OrigUID]

| All UID | B count | C Count |
+---------+---------+---------+
U1 | 1 | 1 |
U2 | 0 | 2 |
U3 | 0 | 0 |

What I have coded right now is:
SELECT T1.[UID],
(Select Count(*) FROM T1 AS test WHERE (test.[Type]='B') and
(test.[UID] = T1.[UID])) AS [B Count],
(SELECT Count(*) FROM T1 AS test1 WHERE (test1.[Type]='C') and
(test1.[origUID] = T1.[origUID])) AS [C Count]
FROM Table
GROUP BY T1.[UID], T1.[origUID];

The results I got:
| All UID | B count | C Count |
+---------+---------+---------+
U1 | 1 | 0 |
U1 | 1 | 2 |
U3 | 0 | 2 |
U3 | 0 | 2 |

Please advise! TIA!

regards,

[corey lawson]

be********@gmail.com wrote:

Hi All,

This is what I'm trying to accomplish:

I have a table T1 with the following fields
- [UID]
- [OrigUID]
- [Type] (where type can be either B or C)

| UID | OrigUID | Type |
+-----+---------+------+
U1 | | B |
U1 | U1 | C |
U3 | U1 | C |
U3 | U2 | C |
U3 | U2 | C |

What I want to do is to get:
- the count of B in [Type] as [B count]for each distinct [UID]
- the count of C in [Type] as [C count]for each distinct [OrigUID]
- [All UID] where it combines the distinct [UID] and distinct [OrigUID]

| All UID | B count | C Count |
+---------+---------+---------+
U1 | 1 | 1 |
U2 | 0 | 2 |
U3 | 0 | 0 |

What I have coded right now is:
SELECT T1.[UID],
(Select Count(*) FROM T1 AS test WHERE (test.[Type]='B') and
(test.[UID] = T1.[UID])) AS [B Count],
(SELECT Count(*) FROM T1 AS test1 WHERE (test1.[Type]='C') and
(test1.[origUID] = T1.[origUID])) AS [C Count]
FROM Table
GROUP BY T1.[UID], T1.[origUID];

The results I got:
| All UID | B count | C Count |
+---------+---------+---------+
U1 | 1 | 0 |
U1 | 1 | 2 |
U3 | 0 | 2 |
U3 | 0 | 2 |

Please advise! TIA!

regards,

create a query like this:

select uid, type, sum(countoftype)
from (
select uid, type, count(type) as countoftype
from mytable
union all
select origuid, type, count(type) as countoftype
from mytable)
....and then base a cross-tab query on it, with uid as a Row Value, type
as the Column value, and the 3rd column as the Value...