Order table based on specific column count

I have a projects table with the following columns id, project_name, remix_of
A project can be a remix of another project and the id of the project being remixed is stored in the remix_of column

id | project_name | remix_of ---------------------------- 1 | 1st Project | 0 2 | 2nd Project | 0 3 | 3rd Project | 2 4 | 4th Project | 1 5 | 5th Project | 2

I want to query all the projects, ordering by the amount of times it has been remixed, in this case it would be:

2nd Project | 2 remixes 1st Project | 1 remix 3rd Project | 0 4th Project | 0 5th Project | 0

I'm using MySQL and have little experience with SQL overall.

-------------Problems Reply------------

You can do this with a left join or correlated subquery:

select p.project_name,
(select count(*) from projects pr where pr.remix_of = p.id) as remixes
from projects p
order by remixes desc;

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,project_name VARCHAR(20) NOT NULL
,remix_of INT NULL
);

INSERT INTO my_table VALUES
(1,'1st Project',NULL),
(2,'2nd Project',NULL),
(3,'3rd Project',2),
(4,'4th Project',1),
(5,'5th Project',2);

SELECT x.project_name
, COUNT(y.id) remixes
FROM my_table x
LEFT
JOIN my_table y
ON y.remix_of = x.id
GROUP
BY x.project_name;
+--------------+---------+
| project_name | remixes |
+--------------+---------+
| 1st Project | 1 |
| 2nd Project | 2 |
| 3rd Project | 0 |
| 4th Project | 0 |
| 5th Project | 0 |
+--------------+---------+

To query based on number of remixes, you can group by remix_of and get the COUNT(*):

SELECT remix_of AS project_id, COUNT(*) AS numRemixes
FROM myTable
GROUP BY project_id;

Then, if you need to get the values from the original table, just do a JOIN. You may want a left join to get 0 values:

SELECT m.id, m.project_name, COALESCE(mt.numRemixes, 0) AS numRemixes
FROM myTable m
LEFT JOIN(
SELECT remix_of AS project_id, COUNT(*) AS numRemixes
FROM myTable
GROUP BY project_id) mt ON mt.project_id = m.id
ORDER BY numRemixes DESC;

Category:mysql Views:1 Time:2019-01-10
Tags: mysql sql laravel

Related post

Copyright (C) dskims.com, All Rights Reserved.

processed in 0.088 (s). 11 q(s)