0

I have one mySQL table here with some data.

mySQL table here

Before asking for help I tried MySQL Nested Select Query? before, but it's not working for me.

So, what I want to do is to list the total of the dir_id with more doc_id. The table should have only two columns as a result. dir_id and total. In this case, the table results shall have for the 1st-row dir_id = 3 and total = 3. In other words, dir_id 3, has 3 doc_id.

I tried to make use of DISTINCT and COUNT to construct a nested query as follow:

SELECT COUNT(*), AS total `dir_id` 
FROM
    (SELECT DISTINCT dir_id, doc_id 
    FROM `dir_subdir` 
    ORDER BY `dir_subdir`.`dir_id` DESC)

Here is the message error I got after running the query. Unfortunately, it is not clear for me, so I cannot fix it.

Any help will be grateful. Thank you!

ack2v
  • 25
  • 2
  • 6
  • Did you get an error? You should share the exact text of the error if you did (hint: you did get an error, if the query you show is transcribed accurately). – Bill Karwin Jun 26 '20 at 00:33
  • I posted the MySQL message error as an update. – ack2v Jun 26 '20 at 10:22

1 Answers1

0

You're missing GROUP BY dir_id. You also have the comma misplaced.

You can do this without the subquery using COUNT(DISTINCT column)

SELECT dir_id, COUNT(DISTINCT doc_id) AS total
FROM dir_subdir
GROUP BY dir_id
ORDER BY dir_id DESC
Barmar
  • 741,623
  • 53
  • 500
  • 612