Question

need help fixing this code.
also need a screenshot of it running
thank you

/* Creating table. */
CREATE TABLE HOM(story_name varchar (255), [user] char(50), age int, reading_counts int NOT NULL, CONSTRAINT Story_User PRIMARY KEY (story_name,[user]));
INSERT INTO HOM VALUES ('Humpty Dumpty', 'Andrea', 5, 10);
INSERT INTO HOM VALUES ('Wheels on the Bus', 'Simon', 3, 15);
INSERT INTO HOM VALUES ('Baa Baa Black Sheep', 'Leo', 7, 4);
INSERT INTO HOM VALUES ('Jack and Jill', 'James', 4, 11);
INSERT INTO HOM VALUES ('Three Blind Mice', 'Leslie', 6, 12);
INSERT INTO HOM VALUES ('Humpty Dumpty', 'Simon', 3, 8);
INSERT INTO HOM VALUES ('Three Blind Mice', 'Andrea', 5, 7);
INSERT INTO HOM VALUES ('Wheels on the Bus', 'Andrea', 5, 11);
/* View table.*/
SELECT * FROM HOM;

/* Generates most popular book*/

SELECT * FROM HOM
INNER JOIN
(SELECT DISTINCT [user], MAX(reading_counts) AS Max_reads FROM HOM GROUP BY [user]) b
ON HOM.[user] = b.[user]
AND HOM.reading_counts = b.reading_counts;

Answer 1

Dear Student ,

As per the requirement submitted above , kindly find the below solution.

This demonstration is using SQL Server 2014. Table is created in SQL Server Management Studio (SSMS). Below is the screen in SSMS for Table HOM.

SQL Query :

/* Generates most popular book*/

select story_name,sum(reading_counts) As Max_reads
from HOM
group by story_name order by Max_reads desc
offset 0 rows
fetch next 1 rows only;

Screen in SSMS :

NOTE : PLEASE FEEL FREE TO PROVIDE FEEDBACK ABOUT THE SOLUTION.