Hi Im trying to count the number of appointment we make per our location so I have a appointments table and a sites table so where I run this query I get
one column with the date and the second column with a count , now I also wanted to include the site contract number but as that would not work with grouping I added a where So I could then run it once for each site but when I add the contract_number to the where I lose the count,
Any help would be great Im new to oracle by Ive used mysql for years what I want to do is get a query were I can join two table via a contract_number (SITE ID) and I want to group by appointment date and have 3 column returned
the count and the contract_number and the date .
so I can show how many appointment each site had on a date,
FIRST QUERY
------------------------
select TO_CHAR(APPOINTMENTS.APPOINTMENT_DATE_TIME, 'MM-DD-YYYY') as APPOINTMENT_DATE_TIME,
count(DISTINCT APPOINTMENTS.SITE_ID) AS count
from SITES SITES,
APPOINTMENTS APPOINTMENTS
where APPOINTMENTS.SITE_ID=SITES.CONTRACT_NUMBER
group by APPOINTMENT_DATE_TIME
SECOND QUERY
-------------------------
select TO_CHAR(APPOINTMENTS.APPOINTMENT_DATE_TIME, 'MM-DD-YYYY') as APPOINTMENT_DATE_TIME,
count(DISTINCT APPOINTMENTS.SITE_ID) AS count
from SITES SITES,
APPOINTMENTS APPOINTMENTS
where APPOINTMENTS.SITE_ID=SITES.CONTRACT_NUMBER AND SITES.CONTRACT_NUMBER = 'MTNZ8013'
group by APPOINTMENT_DATE_TIME