Oracle SQL - Count, per month, how many times a site appears in the results

Question

I'm not sure if I will explain this correctly so apologies in advance.

I'm looking to put together a report that shows the number of times a site (central_site.site_code & central_site.site_name) appears in a report and then total this up for each month with a grand total at the end. The date to summarize into month values is job.actual_start_date

What I'm looking for is something like:

 Site Code       Site Name        April     May     June     July    August     Total
   1234         HIGH STREET         2        4        3        3        2         14
   3093         4TH AVENUE          10       5        8        8        7         38

The code I have got so far to produce all the information that I would like summarizing in the format above is:

select
central_site.site_code,
central_site.site_name,
job.actual_start_date

from
central_site
inner join job on job.site_code = central_site.site_code
inner join job_type on job.job_type_key = job_type.job_type_key
inner join job_status_log on job.job_number = job_status_log.job_number

where 
job_type.job_type_code = 'G012' and
job_status_log.status_code = '5200'

I just don't know the syntax / formulas to be able to total each site up per month and then provide a total for the year.

Answer 1

I think you want conditional aggregation:

select cs.site_code, cs.site_name,
       sum(case when extract(month from ?.datecol) = 1 then 1 else 0 end) as jan,
       sum(case when extract(month from ?.datecol) = 2 then 1 else 0 end) as feb,
       . . .,
       count(*) as year_total
from central_site cs join
     job j
     on j.site_code = cs.site_code join
     job_type jt
     on j.job_type_key = jt.job_type_key join
     job_status_log jsl
     on j.job_number = js.job_number
where jt.job_type_code = 'G012' and
      jsl.status_code = '5200' and
      ?.datecol >= date '2018-01-01' and
      ?.datecol < date '2019-01-01'
group by cs.site_code, cs.site_name;

This is assuming that "number of times" is simply a count. Your question doesn't specify *what column is used to specify the date. So, that element needs to be filled in.