CODEWITHSUNDEEP
sqlsql-serverdatabase
Aci75
Aci75

Reputation: 87

Exclude multiple sum rows when all zero

I would like to exclude a row in the query result if all 3 sum columns are zero.

Select Name
,sum(case when cast(Date as date) <= Convert(datetime, '2014-05-01') then 1 else 0 end) as 'First'
,sum(case when cast(Date as date) <= Convert(datetime, '2014-04-01') then 1 else 0 end) as 'Second'
,sum(case when cast(Date as date) <= Convert(datetime, '2013-05-01') then 1 else 0 end) as 'Third'
FROM [dbo].[Posting]
inner join dbo.Names on Name.NameId = Posting.NameId
where active = 1 
group by Name
order by Name

Upvotes: 0

Views: 2138

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

You can repeat the expressions in the having clause:

having sum(case when cast(Date as date) <= Convert(datetime, '2014-05-01') then 1 else 0 end) > 0 or
       sum(case when cast(Date as date) <= Convert(datetime, '2014-04-01') then 1 else 0 end) > 0 or
       sum(case when cast(Date as date) <= Convert(datetime, '2013-05-01') then 1 else 0 end) > 0

However, you could write the conditions more simply as:

having sum(case when cast(Date as date) <= '2014-05-01' then 1 else 0 end) > 0 or
       sum(case when cast(Date as date) <= '2014-04-01' then 1 else 0 end) > 0 or
       sum(case when cast(Date as date) <= '2013-05-01' then 1 else 0 end) > 0

Or, because the first encompasses the other two:

having sum(case when cast(Date as date) <= '2014-05-01' then 1 else 0 end) > 0

Or, even more simply:

having min(date) <= '2014-05-01'

Also, you should use single quotes only for string and date names. Don't use single quotes for column aliases (it can lead to confusion and problems). Choose names that don't need to be escaped. If you have to have a troublesome name, then use square braces.

Upvotes: 1

Behnam
Behnam

Reputation: 1063

you can use this query:

with temp as 

(Select Name
,sum(case when cast(Date as date) <= Convert(datetime, '2014-05-01') then 1 else 0 end) as 'First'
,sum(case when cast(Date as date) <= Convert(datetime, '2014-04-01') then 1 else 0 end) as 'Second'
,sum(case when cast(Date as date) <= Convert(datetime, '2013-05-01') then 1 else 0 end) as 'Third'
FROM [dbo].[Posting]
inner join dbo.Names on Name.NameId = Posting.NameId
where active = 1 
group by Name
order by Name)

select * from temp where [first]+[second]+[third]=0

Upvotes: 1

Deep
Deep

Reputation: 3202

this may work for you :

select * from
( 
.......your query......
) as t
where First <> 0 or Second <> 0 or Third <> 0

Upvotes: 3

Dgan
Dgan

Reputation: 10285

You don't need to Convert date to datetime.

cast(Date as date) will convert columns date values to yyyy-MM-dd format

with CTE
as
(select 
sum(case when cast(Date as date) <=  '2014-05-01' then 1 else 0 end) as 'First'
,sum(case when cast(Date as date) <=  '2014-04-01' then 1 else 0 end) as 'Second'
,sum(case when cast(Date as date) <=  '2013-05-01' then 1 else 0 end) as 'Third' from myTable
)
where CTE.First=0 and CTE.Second=0 and CTE.Third=0

Upvotes: 0

Related Questions