July 28, 2005 at 9:49 am
I am trying to write a VIEW that will show 3 columns:
A text field on which I group by.
A separate field which I do a COUNT on.
A separate field that I need to do a conditional COUNT on.
Is it possible to do the conditional count? If you are in I can stop by.
CREATE VIEW dbo.vw_1
AS
SELECT SourceFile,
COUNT(ID) AS Records,
COUNT(Processed) AS RecordsProcessed
FROM dbo.CW_PDUSummaries
GROUP BY SourceFile
July 28, 2005 at 10:04 am
I should have clarified a little further…
I need to do something like this, I think…
SELECT SourceFile,
COUNT(ID) AS Records,
COUNT(** where the Processed column is not null **) AS RecordsProcessed
FROM dbo.tbl_1
GROUP BY SourceFile
July 28, 2005 at 10:50 am
Well for this instance, Count(*) counts everything including nulls, Count(Processed) counts only non null values.
If you need to differentiate between Null, and other values then use a case statement
-- Create test table
create table test(pk int identity, Name varchar(100), Processed bit)
-- Insert test values
insert into Test(name, Processed)
select 'Foo', NULL
union all
select 'Foo', 1
union all
select 'Widget', 1
union all
select 'Widget', 0
union all
select 'Fux', 0
union all
select 'Fux', 1
union all
select 'Capacitor', NULL
-- query only using nulls
select name, count(*), count(Processed)
from Test
group by Name
-- query using case.
select name, count(*), sum(case when Processed is null then 0 else processed end)
from Test
group by Name
drop table Test
July 29, 2005 at 2:44 am
You can take advantage of COUNT ignoring NULL like this:
SELECT SourceFile,
COUNT(ID) AS Records,
COUNT(NULLIF(Processed,'some value to exclude')) AS RecordsProcessed
FROM dbo.tbl_1
GROUP BY SourceFile
Andy
July 29, 2005 at 7:33 am
By conditional counts i thought this is what you meant:
select
Count(ID) as TotalRecords
case when isnull(Processed,0) = 0 then 1 else 0 end as RecordsNotProcessed,
case when isnull(Processed,0) = 1 then 1 else 0 end as RecordsAlreadProcessed,
from CW_PDUSummaries
Lowell
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply