Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Group by Count of Counts

This is my script

SELECT COUNT( [Id]) as [Count Of Register],
Tag as [Tag]
FROM [Members]
Group By Tag 
Order By [Count Of Register] Desc;

Returned table is like this:

Count   Tag

550 ----1
550 ----2
545 ----3
545 ----4
545 ----5

So, this time I need Count of Tag, Group by this new Count field.

Some Returned Values Like:

2 ---550
3 ---545

Is there any way without using new table or Template Table or any Storage Table just by query?

like image 993
Saeid Avatar asked Dec 01 '11 08:12

Saeid


People also ask

Can we use COUNT in GROUP BY in SQL?

The GROUP BY statement is often used with aggregate functions ( COUNT() , MAX() , MIN() , SUM() , AVG() ) to group the result-set by one or more columns.

Can we use COUNT and GROUP BY together?

The use of COUNT() function in conjunction with GROUP BY is useful for characterizing our data under various groupings. A combination of same values (on a column) will be treated as an individual group.

Does COUNT work without GROUP BY?

Using COUNT, without GROUP BY clause will return a total count of a number of rows present in the table. Adding GROUP BY, we can COUNT total occurrences for each unique value present in the column. we can use the following command to create a database called geeks.

What does COUNT (*) do in SQL?

COUNT(*) returns the number of items in a group. This includes NULL values and duplicates. COUNT(ALL expression) evaluates expression for each row in a group, and returns the number of nonnull values.


2 Answers

SELECT [Count Of Register], COUNT(1) [Grouped Count]
FROM
(
    SELECT COUNT( [Id]) as [Count Of Register],
           Tag as [Tag]
    FROM [Members]
    Group By Tag 

) MyTable
GROUP BY [Count Of Register]
like image 76
Dave D Avatar answered Sep 23 '22 23:09

Dave D


You could use

SELECT [Count Of Register], COUNT(*) FROM
    (SELECT COUNT([Id]) as [Count Of Register], Tag as [Tag]
     FROM [Members] GROUP BY Tag) q
GROUP BY [Count Of Register]
like image 41
Marco Avatar answered Sep 25 '22 23:09

Marco