i want to create this query:

Consider the users divided in three age groups: <=30, >30 <= 50, and >50. For each group, report the average book rating. For each group report also the number of users within the group

i have these tables

DROP TABLE IF EXISTS dbo.users
DROP TABLE IF EXISTS dbo.movies
DROP TABLE IF EXISTS dbo.books
DROP TABLE IF EXISTS dbo.books_ratings
DROP TABLE IF EXISTS dbo.movies_ratings
DROP TABLE IF EXISTS dbo.m_genres
DROP TABLE IF EXISTS dbo.movies_genres
DROP TABLE IF EXISTS dbo.countries

create table dbo.users (
userid int primary key,
nationality varchar(50) not null,
age int not null
);

create table dbo.movies (
movieid int primary key,
movietitle char(200) not null
);

create table dbo.books (
isbn char(50) primary key,
title char(400) not null,
author char(400) not null,
yearofpublication int not null CHECK ( yearofpublication >= 0 and yearofpublication<2100),
publisher nchar(500) not null
);

create table dbo.books_ratings (
userid int not null,
isbn char(50) not null,
b_ratings int not null,
b_date_rating date not null,
primary key(userid,isbn)
);

create table dbo.movies_ratings (
userid int not null,
movieid int not null,
m_ratings float not null,
m_date_rating date not null,
primary key(userid,movieid)
);

create table dbo.m_genres (
codegenre int primary key,
namegenre varchar(50) not null
);

create table dbo.movies_genres (
movieid int not null,
genreid int not null
);

create table dbo.countries (
country varchar(60) not null,
code varchar(3) primary key
);

I want to archive to report the average book rating and for each group to report the number of users within the group

Name: i want to create this query: Consider the users divided in three age groups: <=30, >30 <
Brand: Study Help Me
SKU: 103876
Price: 10 USD
Availability: LimitedAvailability
Rating: 5 (3 reviews)

pur-new-sol

Purchase A New Answer

Custom new solution created by our subject matter experts

GET A QUOTE

Answer Preview

SELECT Books.title, AVG(books_ratings.b_ratings)
FROM dbo.Books
JOIN books_ratings 
ON books.isbn = books_ratings.isbn
GROUP BY books.isbn, Books.title;

SELECT 
		SUM(CASE WHEN age <= 30 THEN 1 ELSE 0 END) AS [Under 30],
        SUM(CASE WHEN age BETWEEN 31 AND 50 THEN 1 ELSE 0 END) AS [Between 30 and 50],
        SUM(CASE WHEN age > 50 THEN 1 ELSE 0 END) AS [Over50]
FROM users;

Step-by-step explanation

1. In code segment one, we will select the Book title and the Aggregate average of the Ratings. we will then join the Books to the Books Ratings table via common column ISBN.

We will then group the result by the ISBN number.

2. For the second block, we group the ages according to their clustered ages. We count the ages according to their ranges by the use of SUM() function.