CODEWITHSUNDEEP
sqlsql-serverpivot
Aayush Rajopadhyaya
Aayush Rajopadhyaya

Reputation: 103

Count a column according to another column and display the count in different table

This is table A

Wallet Type       State
------------------------
106                1
106                2
106                1
106                1
106                2
112                1
112                2 
112                2

Now i need a table where it counts wallet type according to the state

Table B would be like this

    State       Distributor(106)  Agent(112)
    ----------------------------------------
    1                  3             1
    2                  2             2

Upvotes: 1

Views: 85

Answers (5)

DineshDB
DineshDB

Reputation: 6193

Try this:

You can get this using Aggregate functions.

SUM:

SELECT State
    ,SUM(CASE WHEN Wallet_Type = 106 THEN 1 ELSE 0 END) Distributor_106
    ,SUM(CASE WHEN Wallet_Type = 112 THEN 1 ELSE 0 END) Distributor_112
FROM Your_Table
WHERE Wallet_Type IN (106, 112)
GROUP BY State

COUNT:

SELECT State
    ,COUNT(CASE WHEN Wallet_Type = 106 THEN 1 END) Distributor_106
    ,COUNT(CASE WHEN Wallet_Type = 112 THEN 1 END) Distributor_112
FROM Your_Table
WHERE Wallet_Type IN (106, 112)
GROUP BY State

Upvotes: 1

Zeeshan Adil
Zeeshan Adil

Reputation: 2125

First of All, you need to declare a cols Variable that will give you all the distinct Wallet Types that will be used later in the query:

 DECLARE @cols AS NVARCHAR(max) =

     Stuff((SELECT DISTINCT ', ' + Convert(nvarchar, WalletType) 
     FROM [IHC].[dbo].[TABLENAMEHERE]
     FOR XML PATH ('')), 1, 2, '')

what it does is, it gets all the distinct comma separated WalletTypes. as XML Path gives you data in XML so we can play with it.

then use PIVOT operator as suggested by some people. here:

        select *
from 
(
 select [state], [WalletType], count([WalletType]) as count FROM [IHC].[dbo].[TABLENAMEHERE]
group by [state], [WalletType]
) SourceTable
pivot
(
  sum(count)
  for [WalletType] in (@cols)
) piv;

the first part of this query gives you state, WalletType, 'Count of Wallet' Types grouped by state and Wallet Type or say broken w.r.t State and Wallet Type

like this:

state WalletType count

1 106 4

2 106 2

1 112 1

2 112 2

now this 'Count of Wallet' as count is used in PIVOT function to get the SUM of COUNT w.r.t the Wallet Types so it sums the count w.r.t WalletType and gives the final result

make sure you use it in a dynamic query.

Upvotes: 0

DxTx
DxTx

Reputation: 3357

Try this one... (Dynamic PIVOT)

DECLARE @cols AS NVARCHAR(max) = Stuff((SELECT DISTINCT ', ' + Quotename(WalletType) 
         FROM   TableName
         FOR xml path(''), type).value('.', 'NVARCHAR(MAX)'), 1, 1, ''); 

DECLARE @query AS NVARCHAR(max) = ' SELECT *
                                    FROM   TableName
                                           PIVOT ( Count(wallettype) 
                                                 FOR wallettype IN ('+@cols+') ) pvt';

EXECUTE(@query)

Output

+-------+-----+-----+
| State | 106 | 112 |
+-------+-----+-----+
|     1 |   3 |   1 |
|     2 |   2 |   2 |
+-------+-----+-----+

Upvotes: 0

Padmasri Raman
Padmasri Raman

Reputation: 26

You can use pivot table for this case

declare @t1 table (wallet_type int, state int)
insert into @t1
values (106,             1),
(106        ,     2       ),
(106        ,     1       ),
(106        ,     1       ),
(106        ,     2       ),
(112        ,     1       ),
(112        ,     2      ),
(112         ,       2    )
--select * from @t1

select *
from 
(
 select state, wallet_type, count(wallet_type) 'count' from @t1 
group by state, wallet_type
) src
pivot
(
  sum(count)
  for wallet_type in ([106], [112])
) piv;

Online Demo: http://rextester.com/QBTOQ8569

Upvotes: 0

DhruvJoshi
DhruvJoshi

Reputation: 17126

These type of problems can also be solved using PIVOT keyword in SQL server. To know more about PIVOT read here.

see working demo

Your query using PIVOT should be like

select 
    [state],
    [Distributor(106)]=[106],
    [Agent(112)]=[112]
from
(
    select 
        [state], 
        [cstate]=state, -- created duplicate as this will be consumed in count operation and will not be available as output column later
        [Wallet Type] 
    from tableA
) src
pivot
(
    count(cstate) for [Wallet Type] in ([106],[112])
    )p

Upvotes: 0

Related Questions