CODEWITHSUNDEEP
sqlsql-servercountsubquery
Kaan Tursucu
Kaan Tursucu

Reputation: 9

Sub query Count SQL

I have this code I need to have row count off. I tried to count it but comes as Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

Can you help me with row count, thank you.

select count((
    select a.GIRIS_ZAMANI, a.CIKIS_ZAMANI, a.PLAKA, a.UCRET, b.KAMERA_ADI
    from ARAC_CIKIS a 
    left join KAMERALAR b 
    on b.KAMERA_ID = a.CIKIS_KAMERA_ID
    where a.CIKIS_ZAMANI between 
        (select cast(cast(sysutcdatetime() as date) as datetime) + cast('00:00:00' as datetime)) and 
        (select cast(cast(sysutcdatetime() as date) as datetime) + cast('23:59:59' as datetime)) and 
        a.UCRET>0
    )
)

Upvotes: 0

Views: 64

Answers (2)

Fahmi
Fahmi

Reputation: 37473

You can try below -

select count(*)
from
(
select a.GIRIS_ZAMANI, a.CIKIS_ZAMANI, a.PLAKA, a.UCRET, b.KAMERA_ADI  from ARAC_CIKIS a left join KAMERALAR b on b.KAMERA_ID = a.CIKIS_KAMERA_ID
  where a.CIKIS_ZAMANI between cast(cast(sysutcdatetime() as date) as datetime) 
    + cast('00:00:00' as datetime) and cast(cast(sysutcdatetime() as date) as datetime) 
    + cast('23:59:59' as datetime)
 and  a.UCRET>0
) A

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269493

Why are you using a subquery?

select count(*)
from ARAC_CIKIS a left join
     KAMERALAR k
     on bkKAMERA_ID = a.CIKIS_KAMERA_ID
where a.CIKIS_ZAMANI between cast(cast(sysutcdatetime() as date) as datetime) + cast('00:00:00' as datetime) and
                             cast(cast(sysutcdatetime() as date) as datetime) + cast('23:59:59' as datetime) and
      a.UCRET > 0

Upvotes: 0

Related Questions