BillyCode BillyCode - 1 month ago 7
SQL Question

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.

Answer

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;