I want to count unique BOOKING_NUMBERS, to display the total next to each STAFF_ID; I'm using the following statement:
SELECT c.Staff_Id , c.(COUNT Booking_Number) AS "Number of Lessons"
FROM AM_Lessons c JOIN AM_Staff m
ON (c.Staff_Id = m.Staff_Id)
ORDER BY c.Staff_Id
I am getting the following error, despite all Column names and tables being correct
ORA-01747: invalid user.table.column, table.column, or column specification
What is the correct syntax?
You have c.COUNT(Booking_Number), but should be using COUNT(c.Booking_Number) as you need to count an element, not look at a "count" property of an element. The following statement should solve your problem:
SELECT c.Staff_Id, COUNT(c.Booking_Number) AS "Number of Lessons"
FROM AM_Lessons c JOIN AM_Staff m ON (c.Staff_Id = m.Staff_Id)
GROUP BY c.Staff_ID ORDER BY c.Staff_Id
Please note that you then need to group by the items you are not counting, as shown. This is because all columns in the SELECT must also be in the GROUP BY, unless they are an aggregate.
In your question you have specified that you are looking for UNIQUE Booking Number. In that case here is the query -
SELECT c.Staff_Id, COUNT(DISTINCT c.Booking_Number) AS "Number of Lessons"
FROM AM_Lessons c JOIN AM_Staff m
ON (c.Staff_Id = m.Staff_Id)
GROUP BY c.Staff_ID
ORDER BY c.Staff_Id
I think this is what you want:
select
staff_id
, count(1) AS "Number of Lessons"
from (
SELECT
distinct c.Staff_Id,
c.Booking_Number
FROM AM_Lessons c JOIN AM_Staff m ON (c.Staff_Id = m.Staff_Id)
) a
group by staff_id
ORDER BY c.Staff_Id
c.count(...)