-3

I want the count() function query return value if count is greater than 0 in SQL Server.

I am using this query, but it returns created date-wise count. If I remove created column from group then it causes an error.

select count(*) as error_count 
from error_info 
group by created 
having count(*) > 0 
  and convert(datetime, created)  >= '2022-12-01 10:01:41.000' 
  and convert(datetime, created) < '2022-12-07 08:59:08.290'

As per my requirement, this query should return total records as count between given dates only if records exist in table. it should not return 0 count.

I want total records as count between given dates only if greater than 0.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Rahul
  • 111
  • 3
  • 13
  • 2
    Is the datetime column created really text?, covert takes a lot of time – nbk Dec 07 '22 at 15:38
  • 2
    The `HAVING` is for aggregate clauses, the *`WHERE`* is not non-aggregate clauses. Also, why are you converting your `created` column to a `datetime`? Is it *not* a date and time data type already? if so, that is a fatal and fundamental flaw in your design that you really need to fix. – Thom A Dec 07 '22 at 15:39
  • @nbk no created column is not text its datetime. i have to return as text that's why i have converted it. – Rahul Dec 07 '22 at 15:41
  • Add table structure, data sample and expected results – Horaciux Dec 07 '22 at 15:43
  • 2
    @Rahul the system automatically converts your text string for comparison in datetime, so the convert is obsolete – nbk Dec 07 '22 at 15:43
  • Not that familiar with sql-server but I guess you can't have a HAVING without a group by and since you don't want a group by you should use where.. – P.Salmon Dec 07 '22 at 15:45
  • if i use where then i am not able to put condition like count(*) > 0. actually i want total records count only if count is greater than 0. i have to return total records count between given dates. – Rahul Dec 07 '22 at 15:53
  • @Horaciux Thanks, its working as expected. – Rahul Dec 08 '22 at 15:18

1 Answers1

1

use filter in where clause not in having

  select error_count from (
    select count(*) as error_count 
    from error_info 
    where
     created  >= '2022-12-01 10:01:41.000' 
    and created < '2022-12-07 08:59:08.290') e
   where error_count>0
Horaciux
  • 6,322
  • 2
  • 22
  • 41
  • its giving date wise count. i want total count – Rahul Dec 07 '22 at 15:40
  • add table structure, data sample and expected results – Horaciux Dec 07 '22 at 15:42
  • this will give you no rows when zero error counted – Horaciux Dec 07 '22 at 15:49
  • in this table, there are only 3 columns id (primary key), error details (varchar), created (datetime). this query is returning multiple rows like 1,1,1,3,1,1 in this way but i want total count in only one row. and query should return count only if records exists in between given date. – Rahul Dec 07 '22 at 15:50
  • I was about to post that exact same thing when the message popped up that this answer had an edit – Kevin Dec 07 '22 at 15:50