Link to home
Create AccountLog in
Avatar of tselectro
tselectro

asked on

How can I count the number of times a value exists in a field

Hello, I have two tables. One "header data" and one "data only".

Header table:
[ID]      [DateTime]                        [Comment]
1      2014-01-08 20:10:26.767      Test running 1
2      2014-01-08 20:29:37.203      Test running 2

Data table:
[ID]      [Header_ID]      [Type]
1      1                  'A'
2      1                  'B'
3      1                  'A'
4      1                  'C'
5      2                  'A'
6      2                  'C'
7      2                  'C'

I want to run a query that give me following results (how many times the [Type] field have value 'A','B' or 'C' for every "running".

[ID]      [DateTime]                        [Comment]            [CountA]      [CountB]      [CountC]
1      2014-01-08 20:10:26.767      Test running 1      2            1            1
2      2014-01-08 20:29:37.203      Test running 2      1            0            2
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SELECT
H.ID
,H.DateTime
,H.Comment
,DSummed.CountA
,DSummed.CountB
,DSummed.CountC
FROM [Header Table] H
JOIN
(
  SELECT
  D.[Header_ID]
  ,SUM(CASE WHEN TYPE = 'A' THEN 1 ELSE 0 END)   [CountA]
  ,SUM(CASE WHEN TYPE = 'B' THEN 1 ELSE 0 END)   [CountB]
  ,SUM(CASE WHEN TYPE = 'C' THEN 1 ELSE 0 END)   [CountC]
  FROM
  [Data Table] D
  GROUP BY D.[Header_ID]
) D_Summed
ON H.ID = D_Summed.Header_ID

Open in new window

--Static Pivot example (Always have only have results for type A, B & C)
select ID, dt, Comment, [A], [B], [C]
 from (select h.*, d.[Type]
        from headerTable h
                inner join dataTable d
          on h.ID = d.Header_ID) p
 pivot (count([Type]) for [Type] in ([A], [B], [C])) as pvt
 order by dt;

Open in new window

--Dynamic Pivot example    (Count every type - including 'D')
declare @cols varchar(max);
select @cols = coalesce(@cols + ',','') + '[' + [Type] + ']'
  from (select distinct [Type] from dataTable d1) d2;
 select @cols;

declare @sql varchar(max);
set @sql = 'select ID, dt, Comment, ' + @cols + '
             from (select h.*, d.[Type]
                    from headerTable h
                            inner join dataTable d
                      on h.ID = d.Header_ID) p
             pivot (count([Type]) for [Type] in (' + @cols + ')) as pvt
             order by dt;'
exec (@sql);             

Open in new window

Avatar of tselectro
tselectro

ASKER

Thank you for very fast answer!