CODEWITHSUNDEEP
sqldatabasestatistics
Random42
Random42

Reputation: 9159

SQL statistics query

I want to gather some statistics and I wonder if can take some min, max and average values directly by DB queries.

In an oversimplification I have something like this:

Person  | Account
__________________
Person1 | Account1
Person1 | Account2
Person1 | Account3
Person2 | Account4
Person2 | Account5
..................

I want to find the maximum, minimum and average number of accounts a person has. Is this possible with a SQL query?

Upvotes: 0

Views: 1636

Answers (3)

Krešimir Lukin
Krešimir Lukin

Reputation: 399

You have to group by person and then subquery that, something like this: 

SELECT MIN(PersonCount), MAX(PersonCount), AVG(PersonCount) FROM 
(SELECT Person, COUNT(*) AS PersonCount FROM PersonAccounts GROUP BY Person)

Upvotes: 2

Taryn
Taryn

Reputation: 247610

Yes, you could use aggregate functions to perform this type of operation:

MAX() MIN() AVG()

select max(cnt), min(cnt), avg(cnt)
from
(
    select person, count(*) cnt
    from yourtable
    group by person
) x

Upvotes: 2

podiluska
podiluska

Reputation: 51494

select
    min(accountcount),
    avg(accountcount),
    max(accountcount)
from
(
    select 
        person, count(*) as accountcount
    from 
        yourtable
    group by person
) v

AVG will return the mean, as opposed to any other kind of average.

Upvotes: 6

Related Questions