SQL count and join statement

Question

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?

score 1 · Accepted Answer · edited May 23 '17 at 11:52

1

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.

edited May 23 '17 at 11:52

Community

1
1

answered Jun 02 '14 at 18:27

David Manheim

2,553
2
27
42

1

Yours worked perfectly, Thanks David. I could use your help for a few more of the questions I am working with. – Uhujkill Jun 02 '14 at 18:32
Good luck with SQL; if you post questions, you can get answers, but I think your main issue is that you're not fully comfortable with SQL syntax and logic. You may want to do some background reading to understand how this works. In this case, you were simply reversing the c. and count, but based on your query, I think you're missing how the logic of the SQL statement works as well. – David Manheim Jun 02 '14 at 18:38
Thanks for the help and advice, it's great to see helpful people on the Internet. – Uhujkill Jun 02 '14 at 18:40
If you want to accept my answer, that would be appreciated. (The green check-mark.) Also, if you plan to have future questions, make an account and feel free to learn more and participate! – David Manheim Jun 02 '14 at 18:47

score 1 · Answer 2 · edited May 29 '16 at 09:41

1

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

edited May 29 '16 at 09:41

Fabian N.

3,807
2
23
46

answered Jun 02 '14 at 18:33

TMNT2014

2,102
1
11
13

score 0 · Answer 3 · answered Jun 02 '14 at 18:29

0

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

answered Jun 02 '14 at 18:29

Kevin Postlewaite

595
4
14

You're just doing a unique sort here for no reason. The error is `c.count(...)` – Ben Jun 02 '14 at 18:31
Thanks for the help my friend, just a few syntax issues but got there in the end. Thanks again. – Uhujkill Jun 02 '14 at 18:32
It's possible that the fact that he is not using unique is a logic error, but if the table structure is properly normalized, you probably don't need to ask for a distinct value. – David Manheim Jun 02 '14 at 18:41

SQL count and join statement

3 Answers3