-- create a sample table variable
declare @Sample table( A char(1), B char(1))

-- insert sample data
insert @Sample
select 'Y', '1' union all
select 'Y', '1' union all
select 'Y', '2' union all
select 'Y', '3' union all
select 'Y', '4' union all
select 'Y', '4' union all
select 'Y', '4' union all
select 'N', '1' union all
select 'N', '1' union all
select 'N', '1' union all
select 'N', '2' union all
select 'N', '2'

--I would like the output to be 

----------------------------------------------------
--| Col A | Num of Col A | Col B | Num of Col B |
----------------------------------------------------
--| Y | 7 | 1 | 2 | 
--| Y | 7 | 2 | 1 |
--| Y | 7 | 3 | 1 |
--| Y | 7 | 4 | 3 |
--| N | 5 | 1 | 3 |
--| N | 5 | 2 | 2 |
----------------------------------------------------

-- try a solution using derived tables
select
dt1.A,
dt1.NumOfColA,
dt2.B,
dt2.NumOfColB
from
(
select
A,
count(*) as NumOfColA
from @Sample
group by A
)dt1
left join
(
select
A,
B,
count(*) as NumOfColB
from @Sample
group by A,B
)dt2
on dt1.A = dt2.A

-- result
A    NumOfColA   B    NumOfColB
---- ----------- ---- -----------
N    5           1    3
N    5           2    2
Y    7           1    2
Y    7           2    1
Y    7           3    1
Y    7           4    3

(6 row(s) affected)

quote:

---

Originally posted by 850t

hi all,

I have a question related to a table that I'm trying to query.

Table A
---------------------
| Col A | Col B |
---------------------
| Y | 1 |
| Y | 1 |
| Y | 2 |
| Y | 3 |
| Y | 4 |
| Y | 4 |
| Y | 4 |
| N | 1 |
| N | 1 |
| N | 1 |
| N | 2 |
| N | 2 |
---------------------

I would like the output to be

--------------------------------------------------
| Col A | Num of Col A | Col B | Num of Col B |
--------------------------------------------------
| Y | 7 | 1 | 2 |
| Y | 7 | 2 | 1 |
| Y | 7 | 3 | 1 |
| Y | 7 | 4 | 3 |
| N | 5 | 1 | 3 |
| N | 5 | 2 | 2 |
--------------------------------------------------

What's the query to do that?

---

SELECT ColA,
Occ AS [Num of ColA],
ColB,
COUNT(*) AS [Num of ColB]]
FROM
(
SELECT COUNT(1) OVER (PARTITION BY ColA) AS Occ,ColA,ColB
FROM table
)t
GROUP BY ColA,Occ,ColB