CODEWITHSUNDEEP
sqlms-access
user2907249
user2907249

Reputation: 879

Count if or Count where in SQL

I have a data set of contracts and the nationalities of people working on them. A sample is as follows. 

Contract  Country
GTT001    DE
GTT001    DE
GTT001    US
BFF333    US
BFF333    US
BFF333    DE
HHH222    GB
HHH222    GB
HHH222    GB

I need a query that will count the number of people working on each contract from each country. So one that will produce a table like below:

         DE   US    GB
GTT001   2    1     0
BFF333   1    2     0
HHH222   0    0     3

I am working in Access 2010. Is there a countif or some equivalent that will allow me to count values based on conditions?

Upvotes: 4

Views: 162

Answers (3)

T McKeown
T McKeown

Reputation: 12857

PIVOT will work, it's a bit more complicated but so raw sql you could GROUP BY, for example:

SELECT Contract, Country, COUNT(*)
FROM [YourTable]
GROUP BY Contract, Country
ORDER BY Country

Upvotes: 0

Valentin Petkov
Valentin Petkov

Reputation: 1648

   DECLARE @Pivotcols AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX)
      select @Pivotcols = STUFF((SELECT distinct N',' + QUOTENAME([Country]) 
                from [Contract]
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'')

    select @Pivotcols

    set @query = N'SELECT [Contract], ' + @Pivotcols + N' 
        from 
        (
           SELECT   [Contract]
  ,[Country]
   FROM [TEST_DB].[dbo].[Contract]

        ) sourceTable
        pivot 
        (
            Count([Country])
            for [Country] in (' + @Pivotcols + N')
        ) p '

  execute sp_executesql @query;


The core query

  SELECT * from
  (SELECT   [Contract]
     ,[Country]
   FROM [TEST_DB].[dbo].[Contract]
  ) sT
    pivot 
        (
            Count([Country])
            for [Country] in ([DE],[US],[GB])
        ) p

Upvotes: 0

Kieveli
Kieveli

Reputation: 11075

You want to use GROUP BY using both contract and then country. This will give you a list like this:

Contract  Country   Count
GTT001    DE        2
GTT001    US        1
BFF333    US        2
BFF333    DE        1
HHH222    GB        3

Then you want to pivot those values to get it into the format you want. The 0s will still be missing...

Upvotes: 3

Related Questions