Sub query Count SQL

Question

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
    )
)

Help us help you - please share some sample data and the result you're trying to get for it. — Mureinik
– Mureinik, Commented Apr 9, 2020 at 12:05
Does query work without count? I would recommend you test query without counting lines, only with some LIMIT clause at the end and the add SELECT COUNT(*) — 32cupo
– 32cupo, Commented Apr 9, 2020 at 12:15

Fahmi · Accepted Answer · 2020-04-09 12:06:59Z

1

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

answered Apr 9, 2020 at 12:06

Fahmi

37.5k5 gold badges26 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2020-04-09 12:06:17Z

0

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

answered Apr 9, 2020 at 12:06

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

Sub query Count SQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related