CODEWITHSUNDEEP
sqlms-access
Luke101
Luke101

Reputation: 65328

Get total count of a group by query

I am trying to inlude the total count as a column in the query:

SELECT un.user, un.role
FROM [Unique] un group by user, role;

In this query I have 2 columns but I want to add a third column as the total rows calculated in the above query.

Upvotes: 0

Views: 3321

Answers (3)

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52157

This works under MS Access (and is very similar to what would work under Oracle and other databases):

SELECT
    un.user,
    un.role,
    (
        SELECT COUNT(*)
        FROM (
            SELECT un1.user, un1.role
            FROM [Unique] un1 group by user, role
        )
    )
FROM [Unique] un group by user, role;

Here is the actual Oracle syntax:

SELECT
    un."user",
    un.role,
    (
        SELECT COUNT(*)
        FROM (
            SELECT un1."user", un1.role
            FROM "Unique" un1 group by "user", role
        )
    )
FROM "Unique" un group by "user", role;

For the reference, here is the SQL Server specific solution, using CTE:

WITH Q([user], role) AS (
    SELECT un.[user], un.role
    FROM [Unique] un group by [user], role
)
SELECT *, (SELECT COUNT(*) FROM Q)
FROM Q

Upvotes: 1

Philip Sheard
Philip Sheard

Reputation: 5825

If COUNT(*) does not work (and it does not work for every database), you can try COUNT(0) instead. It is quicker than counting the primary key. You might expect COUNT(0) to be 1, but in practice it always returns the rows count. At least it does on every database that I have ever used. And of course you can use any other constant instead of 0 - apart from null.

Upvotes: 0

xQbert
xQbert

Reputation: 35353

SELECT un.user, un.role, 
   (select count(*) from 
      (Select user, role from [unique] group by user, role)) as CNT
FROM [Unique] un 
group by user, role;

Though this is going to have to recount for each roweach time... there is likely a better way.

and I stand by my above comments, you can get this from records sets though most development languages. so having it returned in the query results instead of looking at the meta data is odd to say the least.

Select un.user, un.role
From [unique] un
group by grouping sets ((user, role), ())

Upvotes: 1

Related Questions