CODEWITHSUNDEEP
sqlsql-servert-sql
Philip
Philip

Reputation: 2628

Convert columns into group by/counts

I have the below query that displays the result as follows:

enter image description here

How could I get the result displaying like this bearing in mind that more Client Types could be introduced:

enter image description here

Create Table #temp
(
    [Name] varchar(50),
    Country varchar(50),
    ClientType varchar(50)
)

insert into #temp
(
    Name,
    Country ,
    ClientType
)
select 
    'Joe Bloggs',
    'USA',
    'Client Type 1'
union 
select
    'John Doe',
    'Canada',
    'Client Type 1'
union 
select
    'Jane Smith',
    'USA',
    'Client Type 2'

select
    Country,
    ClientType,
    count(*)
from
    #temp
group by
    Country,
    ClientType

drop table #temp

Upvotes: 0

Views: 75

Answers (4)

GiovaniSalazar
GiovaniSalazar

Reputation: 2094

just you need use PIVOT like this (add to your script before drop #temp) :

Select * from #temp
Pivot (Count(Name) for
ClientType in ([Client Type 1],[Client Type 2]))
As TablaPivot

Upvotes: 2

mkRabbani
mkRabbani

Reputation: 16908

Try with using PIVOT. Below some idea how it should work using PIVOT-

WITH CTE(Country,ClientType,Count)
AS
(
    SELECT 'Canada','Client Type 1',1 UNION ALL
    SELECT 'USA','Client Type 1',1 UNION ALL
    SELECT 'USA','Client Type 2',1
)

SELECT Country,
ISNULL([Client Type 1],0) [Client Type 1],
ISNULL([Client Type 2],0)  [Client Type 2]
FROM
(
SELECT * FROM CTE
) AS P
PIVOT
(
    SUM(Count)
    FOR ClientType IN ([Client Type 1],[Client Type 2])
)AS PVT

Upvotes: 1

Caius Jard
Caius Jard

Reputation: 74605

This kind of operation is typically referred to as a pivot. Many major db vendors have some bespoke syntax that performs it but I tend to do it using standard sql:

SELECT 
  country,
  SUM(CASE WHEN clienttype = 'Client Type 1' THEN 1 END) as ClientType1,
  SUM(CASE WHEN clienttype = 'Client Type 2' THEN 1 END) as ClientType2
FROM
  #temp
GROUP BY country

If you were to run this without the groupby and sum keywords you would get a resultset with the country column (having repeated values) and a clienttype1 column that contains a 1 every time the client type was 1, and a null at all other times, and a clienttype2 column that was 1 whenever the client type was 2 and null at all other times

Grouping on country and summing our columns of mixed null/1 gives the count- null is considered to be zero

On sql server you will see a message "null is eliminated by aggregate operation" but it's an advisory and in this case desired. If it others you you can make the case when have an "else 0" clause have the same effect.

I could also have used COUNT instead of SUM, but for that to work out it really does have to be mixed 1/null because count will consider a value of 0 to be "a countable value" and count it as 1. The nulls are vital if using COUNT

Upvotes: 3

sticky bit
sticky bit

Reputation: 37472

One possibility is to use conditional aggregation only counting a certain client type in a column.

SELECT country [Country],
       count(CASE
               WHEN clienttype = 'Client Type 1' THEN
                 1
             END) [Client Type 1],
       count(CASE
               WHEN clienttype = 'Client Type 2' THEN
                 1
             END) [Client Type 2]
       FROM #temp
       GROUP BY country;

Upvotes: 2

Related Questions