Trouble with integrating COUNT function in SQL query using Oracle

Question

I am trying to self educate myself in SQL in order to better use databases at work. For this purpose I am using Oracle Application Express. This if my first time using the COUNT function and I am having some difficulties integrating it within my query. I have done a great deal of research and read quite a bit of literature but I just can't seem to get it right.

My goal is to display the channel_name and channel_number columns (from the channel table) for each channel along with a count of the number of customers that have that channel as a favorite channel (survey_result column from the survey table). Please see below for code:

SELECT channel.channel_number, 
       channel.channel_name, 
       survey.survey_result, 
       count(SELECT survey.survey_result FROM survey)
  From Channel, survey
 WHERE survey.channel_number = channel.channel_number

Currently I am getting the error message:

ORA-00936: missing expression.

Saharsh Shah · Accepted Answer · 2013-12-19 05:45:23Z

2

Try this:

Below query gives you only those channels which have minimum 1 customer.

SELECT C.channel_number, C.channel_name, COUNT(S.survey_result) NoOfCustomers
FROM Channel C
INNER JOIN survey S ON S.channel_number = C.channel_number
GROUP BY C.channel_number, C.channel_name;

And below query gives you all channels whether it has customer or not.

SELECT C.channel_number, C.channel_name, COUNT(S.survey_result) NoOfCustomers
FROM Channel C
LEFT JOIN survey S ON S.channel_number = C.channel_number
GROUP BY C.channel_number, C.channel_name;

answered Dec 19, 2013 at 5:45

Saharsh Shah

29.1k9 gold badges53 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Madhivanan · Accepted Answer · 2013-12-19 05:43:29Z

2

Either of these may work for you

SELECT channel.channel_number, 
       channel.channel_name, 
       count(survey.survey_result)
  From Channel, survey
 WHERE survey.channel_number = channel.channel_number
 GROUP BY 
       channel.channel_number, 
       channel.channel_name

or

SELECT channel.channel_number, 
       channel.channel_name, 
       survey.survey_result, 
       (SELECT count(survey_result) FROM survey)
  From Channel, survey
 WHERE survey.channel_number = channel.channel_number

answered Dec 19, 2013 at 5:43

Madhivanan

13.7k1 gold badge28 silver badges30 bronze badges

1 Comment

hubson bropa Over a year ago

first option is the way to go

hubson bropa · Accepted Answer · 2013-12-19 05:46:40Z

1

count is an aggregate function thus you should have a group by on channel.channel_number and channel.channel_name. then just use count(survey.survey_result) instead of count(SELECT survey.survey_result FROM survey). Madhivanan's and Saharsh Shah's answers look good to me. including this answer to explain why.

answered Dec 19, 2013 at 5:46

hubson bropa

2,7702 gold badges32 silver badges35 bronze badges

Collectives™ on Stack Overflow

Trouble with integrating COUNT function in SQL query using Oracle

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related