By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,649 Members | 1,560 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,649 IT Pros & Developers. It's quick & easy.

Merging 2 columns from the same table

P: n/a
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,

Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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...
Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.