Shaxib Shaxib - 1 year ago 43
SQL Question

SQL query to calculate average and insert into a table

I'm working on a song archive database and I'm stuck on some queries:


  • Calculating the rating of each user by its average comment score and inserting to the rating in Users

  • Calculating how much Purchases each user has

  • Calculate the average score of a song from the comments table

  • Calculating how much credits each user has spent on his purchases



Below you can find my tables:

create table
Users
(
username nvarchar(30) not null primary key
, pass nvarchar(16)
, email nvarchar(50)
, city nvarchar(10)
, credits int
, rating int
)


create table
Songs
(
song_id int not null identity (1,1) primary key,
song_name nvarchar(30) ,
username nvarchar(30),
genre int,
price int,
song_length int,
listens int
)


create table
Genres
(
genre_id int not null identity (1,1) primary key,
genre_name nvarchar(16)
)


create table
Purchases
(
purchase_id int not null identity (1,1) primary key
, song_id int
, username nvarchar(30)
, date_purchased datetime
)


create table
Comments
(
comment_id int not null identity (1,1) primary key
, username nvarchar(30)
, song_id int
, text nvarchar(30)
, score int
)

Answer Source

I answered some of your questions. In addition to the respective queries I arranged them as common table expressions, which I think could be a convenient way to use them...

Calculating how much credits each user has spent on his purchases, might require to know your logic about how users invest their credits.

    WITH CTE_PurchasesByUser AS
    (
        SELECT p.username as username,  count(*) as NrOfPurchases
        FROM Purchases p
        GROUP BY p.username
    ),
    CTE_AverageScoreBySong AS
    (
        SELECT c.song_id as song_id, (sum(c.score)/count(c.score)) as AverageScore
        FROM Comments c
        GROUP BY c.song_id
    ),
    CTE_AverageScoreByUser AS
    (
        SELECT u.username as username, (sum(c.score)/count(c.score)) as AverageScore
        FROM Users u
        INNER JOIN Comments c ON u.username = c.username
        GROUP BY u.username
    )
    SELECT u.*, ISNULL(bbu.NrOfPurchases,0), asu.AverageScore 
    FROM Users u
    LEFT JOIN CTE_PurchasesByUser bbu ON u.username = bbu.username
    LEFT JOIN CTE_AverageScoreByUser asu ON u.username = asu.username

This SQL ran with your tables, yet I didn't test it with data rows...

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download