Calculate corrected contribution

Transact-SQL
1

Hello SQLTeam forum!

I am new to sql, and my question may be a little dummy.

I have articles and authors, and an article has multiple authors. I have to calculate the corrected contribution,

so for example if author 'A' is in 3 articles,

'X', 'Y', 'Z', and 'X' has 2 authors, 'Y' has 3, 'Z' has one,

the corrected contribution of author 'A' is (1/2 + 1/3 + 1/1).

I managed to calculate the number of authors per article:

SELECT Articles.Title, count(author_article_join.author_id)
FROM [...connections]

GROUP BY Articles.Title;

I know it sound like a school project, but its a part of a quick job from my workplace and its above my sql abilities. I use MS Access.

2

ps. I did it with a table-creating query and working with the new table.

3

Sorry, could you please give us more details? Thanx

4

I have an Author(Id, Name), and an Article(Id, Title, Num of pages etc.) table and a join table to connect them. I have to calculate the upper indicator. Yesterday, after my question, i managed to calculate it, but its may not be the best solution. I made a table-maker query to count the authors grouped by articles, then i worked with the created counted numbers, just sum-ed the 1/counted numbers, and grouped by the name of the Author.

5

contribution across all titles or contribution per title?

create table #authors(authorid int, authorname varchar(150), )

insert #authors
select 1, 'Luke Skywalker'   union
select 2, 'Darth Vader'  union
select 3, 'Han Solo'  union
select 4, 'Cheewee Bakah'

create table #articles(articleid int, title varchar(50)) 
insert into #articles
select 1,'Star Wars' union
select 2,'How to optimize the force' union
select 3,'Death Star: Full or Simple Recovery' union
select 4,'Peace in the universe'

create table #authorarticles(authorid int, articleid int)

insert #authorarticles
--vader and luke on star wars
select 1, 1   union		
select 2, 1   union	
--vader and solo on optimize	
select 2, 2 union  
select 3, 2 union
--all three contributed
select 1, 3 union
select 2, 3 union
select 3, 3 

select title, a.authorname, count(a.authorid) 
  From #authors a
  join #authorarticles aa on a.authorid = aa.authorid
  join #articles ar on ar.articleid = aa.articleid
  group by title, authorname
  order by title, authorname

drop table #authors
drop table #authorarticles
drop table #articles
1 Like
6

You really need to give us the table structures, i.e., all table names and relevant column names. Since you didn't, I had to guess quite a bit. Also had no usable data to test with, so I couldn't test it.

SELECT auth.author_name, CAST(SUM(1.0/author_id_count) AS decimal(6, 2)) AS author_article_count
FROM dbo.author_article_join aaj
INNER JOIN dbo.authors auth ON auth.author_id = aaj.author_id
LEFT OUTER JOIN (
    SELECT title_id, COUNT(author_id) AS author_id_count
    FROM dbo.Articles
    GROUP BY title_id
) AS art ON art.title_id = aaj.title_id
GROUP BY auth.author_name
1 Like
7 
create table #authors(author_id int, author_name varchar(150), )

insert #authors
select 1, 'Luke Skywalker'   union
select 2, 'Darth Vader'  union
select 3, 'Han Solo'  union
select 4, 'Cheewee Bakah'

create table #articles(title_id int, title varchar(50)) 
insert into #articles
select 1,'Star Wars' union
select 2,'How to optimize the force' union
select 3,'Death Star: Full or Simple Recovery' union
select 4,'Peace in the universe'

create table #authorarticles(author_id int, title_id int)

insert #authorarticles
--vader and luke on star wars
select 1, 1   union		
select 2, 1   union	
--vader and solo on optimize	
select 2, 2 union  
select 3, 2 union
--all three contributed
select 1, 3 union
select 2, 3 union
select 3, 3 

SELECT auth.author_name, 
CAST(SUM(1.0/author_id_count) AS decimal(6, 2)) AS author_article_count
FROM #authorarticles aaj
INNER JOIN #authors auth ON auth.author_id = aaj.author_id
LEFT OUTER JOIN (
    SELECT title_id, COUNT(author_id) AS author_id_count
    FROM #authorarticles
    GROUP BY title_id
) AS art ON art.title_id = aaj.title_id
GROUP BY auth.author_name

drop table #authors
drop table #authorarticles
drop table #articles
1 Like
8

Thank you for all of your answers! It works now, i joined the tables with clicking, and something went wrong. I did it by code with sql and used a query similar to @ScottPletcher -s and it works. Thank you again!
I understand now what you mean to give more details. The database is in my language (i just create it and others will use it so i dont use English), and the data is sensitive, but next time if i have to ask, i will try to make a more detailed structure of it.