SQL Select – Get a count of data as a separate column error

I am working on this query whereas I have a table as follows

Code | Date   
-------------
001  | 20-JUN     
001  | 20-JUN    
002  | 20-JUN 
003  | 20-JUN    
002  | 20-JUN   
001  | 20-JUN   
002  | 21-JUN

I need to get a count of codes in a separate column only for the date of 20-JUN

Code | Count
--------------
001  |  3
002  |  2
003  |  1

Here’s the query I am trying.

SELECT
    ll.code,
    (
        SELECT
            COUNT(*)
        FROM
            lab_tests ltb
        WHERE
            ltb.code = ll.code
            AND ltb.date = ll.date
    ) AS Count
FROM
    lab_tests ll
WHERE
    ll.date = '20-JUN'

This however does not give me the desired outcome, except it returns this.

Code | Count
--------------
001  |  1
001  |  1
001  |  1
002  |  1
002  |  1
003  |  1

I still cannot figure out how to change the query to get the outcome I need. Can anyone please help?

Thanks in advance!

Answer

You seem to just want aggregation:

select t.code, count(*) cnt
from mytable t
where t.date = date '2020-06-20'
group by t.code

This assumes that date is stored as a date, and that your database, which you did not tell, supports the standard syntax to declare literal dates.

Advertisement

Answer