Answer to Question #284143 in Databases | SQL | Oracle | MS Access for Lovely

Question

3. Get the number of subscribers for each channel.

Note:

• Sort the output in the descending order of no_of_subscribers, and then in the ascending order of channel_name.

If there are no subscribers for a channel is 0, then keep the no_of_subscribers as 0.

Expected Output Format:

channel Id

channel_name

No_of_subscribers

Answer 1

CREATE TABLE channels (channel_id, channel_name, channel_descr);
CREATE TABLE channel_accesses (channel_id, subscriber_id, access_date, num_of_accesses);
FROM    channels AS c  
LEFT JOIN     channel_accesses AS ca      
ON ca.channel_id = c.channel_id
GROUP BY    c.channel_id, c.channel_name
SELECT     c.channel_id,     c.channel_name,    COALESCE(SUM(ca.num_of_accesses), 0) AS total_num_of_accesses
FROM     channels AS c  
LEFT JOIN     channel_accesses AS ca      
ON ca.channel_id = c.channel_id
GROUP BY    c.channel_id, c.channel_name ;