CODEWITHSUNDEEP
sqldatabaseoracle-databaseoracle10g
Jose Carrillo
Jose Carrillo

Reputation: 1860

Oracle SQL generate query

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.

Upvotes: 1

Views: 262

Answers (3)

bonCodigo
bonCodigo

Reputation: 14361

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.

Upvotes: 2

BellevueBob
BellevueBob

Reputation: 9618

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

Upvotes: 1

skegg99
skegg99

Reputation: 415

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

Upvotes: 1

Related Questions