Oracle SQL generate query

Question

Following is my table schema.

Channel listing table:

 CHAN_NUMBER CHAN_NAME CHAN_TYPE
 ----------- -----------------------------------
 1 MTV Music
 2 ESPN Sports
 3 TNT Movies
 4 Fox Movies
 5 Fox Sports

customer survey table:

 SURV_ID SURV_DATE SURV_FAV_CHAN CUST_NUMBER
 ---------- --------- ------------- -----------
 1 25-NOV-12 1 2
 2 24-NOV-12 2 1
 3 24-NOV-12 3 3
 4 24-NOV-12 4 4
 5 24-NOV-12 5 5
 6 24-NOV-12 1 6
 7 24-NOV-12 2 7
 8 24-NOV-12 3 8
 9 24-NOV-12 4 9
 10 24-NOV-12 5 10
 11 24-NOV-12 1 11

I have these two tables that I need to generate a report that lists every channel and a count of how many customers have selected that channel as their favorite.

On oracle database I got up to the point where I am generating a count of each time a channel was selected as a favorite from the SURVEY table. But I can't figure out how to join them to create a list of channels displaying the channel number, the name and the count of customers who chose it as their favorite.

-- my channel table query
SELECT CHAN_NUMBER, CHAN_NAME FROM CHANNEL;
-- here is how I'm generating the couNt
SELECT COUNT(SURV_FAV_CHAN) FROM SURVEY
GROUP BY SURV_FAV_CHAN HAVING COUNT(SURV_FAV_CHAN) > 1;

ANY HELP WOULD BE AWESOME.

• bonCodigo: It would be great if you could update your question with the expected results :) Please take a look at the sample based answer I have put for you. Once you try it, do comment.

Answer 1

Please check this reference * SQLFIDDLE

You said you want list every channel, and count of how many customers have selected that channel as their favourite.

Let's go from nested to outside. Nested query you count number of customers from Survery table grouping by favourite channel. Every channel means, you need to do a LEFT JOIN on Channels table to get all the records.

Query:

(select c.*, s.ct from 
channel c
left join
(select count(cust_number) as ct
 , surv_fav_chan from survey
group by surv_fav_chan) as s
on c.chan_number = s.surv_fav_chan
;

Results:

CHAN_NUMBER CHAN_NAME CHAN_TYPE CT
1 MTV Music 3
2 ESPN Sports 2
3 TNT Movies 2
4 Fox Movies 2
5 Fox Sports 2

You seem to treat FOX as two channels offering two different types of programmes. So I have left it as it is. If you want to even count customers by channel type then please clarify.

PS: You may ignore the other old table schema in that SQLFIDDLE table sample. NOTE that it is in MYSQL, however this is an ANSI query - so you may apply it to ORACLE as well.

• Jose Carrillo: I get an error that I'm missing a keyword on line 6, with the AS keyword. Thanks for the help. Not sure if it's an Oracle thing.
• bonCodigo: @JoseCarrillo Yes as mentioned it's an oracle thing. So you may remove AS keyword. And please mark every answer that gives you a correct input. So the community would know that there are more than one solution for a question like yours. ;)
• bonCodigo: Here are further different variations of queries for you to play around :) SQLFIDDLE

Answer 2

You can try something like this:

SELECT MAX(CH.CHAN_NUMBER), MAX(CH.CHAN_NAME), COUNT(SRV.SURV_FAV_CHAN) FROM SURVEY SRV 
LEFT JOIN CHANNEL CH ON CH.CHAN_NUMBER = SRV.SURV_FAV_CHAN
GROUP BY SRV.SURV_FAV_CHAN HAVING COUNT(SRV.SURV_FAV_CHAN) > 1;

And you may want to use SUM(SRV.SURV_FAV_CHAN) if you really need the total amount of customers if I understand you question correctly

• Jose Carrillo: thanks Skegg99 this is exactly what I needed. My hat off to you.

Answer 3

Assuming that SURV_FAV_CHAN and CHAN_NUMBER is the relation, use that for your JOIN, so try this:

SELECT CHAN_NUMBER
 , CHAN_NAME
 , COUNT(DISTINCT SURVEY.CUST_NUMBER) AS FAV_CHANNEL_CNT
FROM CHANNEL
LEFT JOIN SURVEY
ON SURVEY.SURV_FAV_CHAN = CHANNEL.CHAN_NUMBER
GROUP BY CHAN_NUMBER, CHAN_NAME

• Jose Carrillo: Thanks Bob this is a format I can understand a lot better and also provides me with the needed answer.