CODEWITHSUNDEEP
sqldatabaseoracle-databasecount
BillyCode
BillyCode

Reputation: 113

Trouble with integrating COUNT function in SQL query using Oracle

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.

Upvotes: 3

Views: 178

Answers (3)

hubson bropa
hubson bropa

Reputation: 2770

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.

Upvotes: 1

Saharsh Shah
Saharsh Shah

Reputation: 29051

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;

Upvotes: 2

Madhivanan
Madhivanan

Reputation: 13700

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

Upvotes: 2

Related Questions