CODEWITHSUNDEEP
mysql
Ruslan Dascal
Ruslan Dascal

Reputation: 41

MYSQL Order by sum of different table

I have two tables:

cat_seriale - which represents the serial categories and provides ID's for each category like: 1, 2, 3, 4, 5.

seriale - which is actual tv serials, and each tv serial falls in one category.

I am trying to 

SELECT * FROM cat_seriale WHERE `id`='1'

and additionally to all columns, display the sum of views column from all rows in seriale table.

If some one can help me out, that would be great.

Thanks in advance.

SCHEMA:

cat_seriale columns: 
Primary Key - catid(int)
catname (varchar)
...

seriale columns:
Primary Key - id(int)
cat (int)
views(int)

I need to select cat_seriale where ID = 1, and select sum of views in seriale columns where cat is same with id from cat_seriale.

Upvotes: 1

Views: 62

Answers (3)

Nick
Nick

Reputation: 10143

+1 for @Mike answer, but this version has more performance:

SELECT c.*, SUM(s.views) as summary
FROM cat_seriale c
LEFT JOIN seriale s ON s.cat = c.catid
WHERE c.id = '1'
GROUP BY c.catid 
ORDER BY summary

Upvotes: 0

Barmar
Barmar

Reputation: 781096

Join the two tables.

SELECT c.*, SUM(s.views) AS views
FROM cat_seriale AS c
LEFT JOIN seriale AS s ON c.catid = s.cat
WHERE c.id = `1`
GROUP BY c.catid

Upvotes: 1

Mike
Mike

Reputation: 2005

Like this:

select *, (select sum(views) from seriale S where S.cat=C.catid) as sum_views
  from cat_seriale C
 where id='1'
 order by sum_views

Upvotes: 1

Related Questions