CODEWITHSUNDEEP
sql
Sinan Yeniel
Sinan Yeniel

Reputation: 19

sort by count in another table

I have 2 sql tables

1.Categories table

-id
-name
-url
-content

2.Entry table

-id
-title
-content
-cat //category
-view
-author

I want to sort all categories by the total number of categories in the entry table

I would like to sort the categories according to the values returned from this query:

  SELECT count(cat) as catcount, 
         cat 
    FROM entry 
GROUP BY cat 
ORDER BY catcount DESC

this query is currently returning two results. 20 places in the area I want to print. I would like to have random fields written in empty fields

  sample data: 
kitap
acil

expected data:
kitap
acil
random category.name 18 piece

Upvotes: 0

Views: 94

Answers (2)

Alex
Alex

Reputation: 741

More optimal way would be to group in subquery and then join on dictionary:

select c.cat_id, c.cat_name, e.cnt
from Categories as c
left join (
 select e.cat_id, count(*) as cnt
 from Entries as e
 group by e.cat_id
) as e
on e.cat_id = c.id
order by e.cnt desc

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270773

Do you just want a left join?

SELECT c.id, COUNT(e.id) as catcount
FROM category c LEFT JOIN
     entry e
     ON c.id = e.cat
GROUP BY c.id 
ORDER BY catcount DESC ;

Upvotes: 2

Related Questions