CODEWITHSUNDEEP
sql
MaryCoding
MaryCoding

Reputation: 664

Inner join - count and sum of field

I have two tables editors and title_editors that I am trying to run an inner join query to show all editors and how many books they edit. I am not getting the desired result but a total sum of all books edited. How can list the name of all editors and how many books they edit and arrange the report so the editor editing the most books is listed first? SQLFIDDLE

Query

SELECT (e.first_name || ' ' || e.last_name) as Editor_Name, SUM(te.editor_order) as Books_Edited FROM editors e
INNER JOIN title_editors te ON te.editor_id = e.editor_id ORDER BY SUM(te.editor_order);

Table Schema

create table editors    (editor_id char(11) not null,   editor_lname varchar(40) not null,  editor_fname varchar(20) not null,  editor_positon varchar(12) null,    phone char(12) null,    address varchar(40) null,   city varchar(20) null,  state char(2) null, zip char(5) null,   ed_boss char(11) null );
create table title_editors  (editor_id char(11) not null,   title_id char(6) not null,  editor_order tinyint null);

Upvotes: 0

Views: 79

Answers (2)

Bob
Bob

Reputation: 1055

In addition to the comment about CONCAT, you are missing your GROUP BY, and a DESC at the end of your ORDER BY to get them sorted the way you want.

SELECT
  CONCAT(e.editor_fname,' ',e.editor_lname),
  SUM(te.editor_order)
FROM
  editors e
INNER JOIN
  title_editors te
  ON te.editor_id = e.editor_id
GROUP BY
  e.editor_fname,
  e.editor_lname
ORDER BY
  SUM(te.editor_order) DESC;

http://sqlfiddle.com/#!9/47bd4/28

Upvotes: 2

Shawn
Shawn

Reputation: 4786

http://sqlfiddle.com/#!9/47bd4/22

Just GROUP your results.

SELECT (CONCAT(e.editor_fname,' ',e.editor_lname)) as Editor_Name
  , SUM(te.editor_order) as Books_Edited 
FROM editors e
INNER JOIN title_editors te 
  ON te.editor_id = e.editor_id 
GROUP BY e.editor_fname,e.editor_lname
ORDER BY Books_Edited;

Upvotes: 1

Related Questions