Need a nested SQL query which takes the count from the first query and calculate a new count

Question

I have an sql query which calculates the number of distinct fileIds in a table. Given below is the query,

SELECT DISTINCT `fileId` AS FileId, Count( FileId ) AS NumberOfTags
FROM `Tag`
GROUP BY `fileId`
ORDER BY NumberOfTags DESC

The output of this query is given below,

 FileId. NumberOfTags

 1  500

 2 500

 3 550

 4 550

 5 550

I need to get the count of number of Files coming under each NumberOfTag value. Sample output should be something similar to this

 NumberOfTags Number of Files

 500 2

 550 3

The results of the first query is pretty large, it has around 3 Million rows. I tried to create a table of the results of the first query but it also failed with an error

ERROR 1206 (HY000): The total number of locks exceeds the lock table size

Can someone please tell me how to create a nested query to do this task.

Thanks in advance.

Denis de Bernardy · Accepted Answer · 2011-05-23 15:22:18Z

3

Not sure I'm getting the question, but it sounds like you need a subquery:

SELECT NumberOfTags, COUNT( FileId ) as NumberOfFiles
FROM (
  SELECT `fileId` AS FileId, Count( FileId ) AS NumberOfTags
  FROM `Tag`
  GROUP BY `fileId`
  ) as rows
GROUP BY NumberOfTags

answered May 23, 2011 at 15:22

Denis de Bernardy

79.2k14 gold badges138 silver badges158 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

kmarks2 · Accepted Answer · 2011-05-23 15:26:02Z

1

Denis query is how you would correctly do it.

Just to explain why it works, when you want to nest a query you create a temporary table from the nested query output by surrounding it with parentheses and assigning it a name with "as."

Select <...> From ( nested query ) as SomeTempTableName;

answered May 23, 2011 at 15:26

kmarks2

4,94611 gold badges54 silver badges77 bronze badges

Collectives™ on Stack Overflow

Need a nested SQL query which takes the count from the first query and calculate a new count

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related