CODEWITHSUNDEEP
sqlsql-server
Sirildo Reyes
Sirildo Reyes

Reputation: 23

SQL - Combining duplicate rows from multiple tables

I need to create a query that returns the top 5 best selling titles. Some titles have multiple authors, creating multiple rows of the same title taking up the spots.

GROUP BY is not grouping the like titles together and DISTINCT doesn't work as well.

select top 5 
    t.sales,
    t.title_name, 
    (a.au_fname + ' ' + a.au_lname) as author,
    (t.price * t.sales) as revenue
from titles t
join title_authors ta on ta.title_id = t.title_id
join authors a on a.au_id = ta.au_id
group by 
    t.title_name, 
    t.sales, 
    (a.au_fname + ' ' + a.au_lname),
    (t.price * t.sales)

order by t.sales desc

outputs: duplicate rows

When I put DISTINCT in front of TOP it still gives me the same output.

I want the titles that are duplicated to be combined, only showing one author.

These are the tables I'm working with: 'titles', 'title_authors', and 'authors' tables

Thanks to @Venkataraman-R for the solution:

with slight changes to his code I have the exact output I'm looking for

SELECT TOP 5 
    t.sales, 
    t.title_name,
    string_agg((a.au_fname + ' ' + a.au_lname),';') as 'author(s)',
    (t.price * t.sales) as revenue
from titles t
join title_authors ta on ta.title_id = t.title_id
join authors a on a.au_id = ta.au_id
group by t.title_name, t.sales, (t.price * t.sales)
ORDER BY  t.sales DESC

solution

Upvotes: 0

Views: 94

Answers (3)

Venkataraman R
Venkataraman R

Reputation: 12959

I am assuming the SQL Server version as 2016 and writing the below query. I am using STRING_AGG for aggregating the author names for titles. 

SELECT TOP 5 t.title_name,string_agg((a.au_fname + ' ' + a.au_lname),';'), max(t.price * t.sales) as revenue
from titles t
join title_authors ta on ta.title_id = t.title_id
join authors a on a.au_id = ta.au_id
group by t.title_name
ORDER BY  revenue DESC

Upvotes: 0

decoder
decoder

Reputation: 926

If you want latest author you can use the following 

SELECT TOP 5 t.sales, t.title_name, (a.au_fname + ' ' + a.au_lname) as author, (t.price * t.sales) as revenue
    FROM titles t
    JOIN (SELECT  [title_id],max([au_id]) AS au_id
        FROM [test].[dbo].[title_authors] GROUP BY dbo.title_authors.title_id ) as ta on ta.title_id = t.title_id
    JOIN authors a on a.au_id = ta.au_id
    GROUP BY  t.title_name,  t.sales, (a.au_fname + ' ' + a.au_lname), (t.price * t.sales)
    ORDER BY  t.sales DESC

Upvotes: 0

Jorge Garcia
Jorge Garcia

Reputation: 2570

Leave author out of your group by clause, calculating best selling titles does not need data from the authors table.

select top 5 t.sales, t.title_name, 
(t.price * t.sales) as revenue
from titles t

group by t.title_name, t.sales,
(t.price * t.sales)

order by t.sales desc

If you need your final result to include a list of authors per title, you can join this result with the authors table later like this:

with combined_title_authors as
(
     select title_id, author_name = STRING_AGG(a.au_fname + ' ' + a.au_lname, ', ')
    from title_authors ta
    inner join authors a on a.au_id = ta.au_id
    group by ta.title_id
)

select top 5 t.sales, t.title_name, a.author_name,
(t.price * t.sales) as revenue 
from titles t

join combined_title_authors a
on a.title_id = t.title_id

group by t.title_name, t.sales,
(t.price * t.sales), a.author_name

order by t.sales desc

http://sqlfiddle.com/#!18/21f2a/16

Upvotes: 1

Related Questions