Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 286
  • Last Modified:

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
0
tselectro
Asked:
tselectro
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this should do:
select h.ID
  , h.[DateTime]                        
 , h.[Comment]
 , sum(case when d.type = 'A' then 1 else 0 end) countA
 , sum(case when d.type = 'B' then 1 else 0 end) countB
 , sum(case when d.type = 'C' then 1 else 0 end) countC
from [Header table] h
left join [Data table] d
   on d.header_id = h.id
group by h.ID
  , h.[DateTime]                        
 , h.[Comment]  

Open in new window

0
 
Barry CunneyCommented:
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

0
 
Louis01Commented:
--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

0
 
tselectroAuthor Commented:
Thank you for very fast answer!
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now