CODEWITHSUNDEEP
sqlsql-serverreporting-servicesssrs-2008
hemanth
hemanth

Reputation: 587

Different Groupings for different columns

I don't know weather it is possible or not.. i have a table with 4 columns for example

Col1     Col2     Col3     Col4
------------------------------------
O1       O1       P1       P1
O2       O1       P3       P1
O2       O2       P4       P1
O1       O3       P2       P2
O3       O3       P5       P3

i need a output like 

Col     Col1Col3     Col2Col4 
------------------------------------
O1       2              1
O2       2              1
O3       1              2

Result should be count(Col3) group by Col1 and Count(Col4) group by Col2. Can this be possible without using "UNION" as i have 5 different groupings to be done.
Can anyone help me on this...

Upvotes: 0

Views: 61

Answers (1)

TechDo
TechDo

Reputation: 18659

Please try:

select 
    col1, Col1Col3, Col2Col4 
From(
    select col1,count(Col3)  Col1Col3 
    from YourTable
    group by Col1
)x join 
(
    select col2, Count(Col4)  Col2Col4 
    from YourTable
    group by Col2
)y on x.Col1=y.col2

OR

select 
    col1, 
    SUM(c13) Col1Col3, 
    SUM(c24) Col2Col4
from(
    select Col1, 1 c13, 0 c24 From YourTable
    union all
    select Col2, 0 c13, 1 c24 From YourTable
)x
group by col1

Upvotes: 2

Related Questions