Need to get Average count

Question

I would like to get average of product=A that a client have. Say inner select return 1,2,1,4,4,4 for 6 clients

I would like to see result as 4 which means the avg product count a client can have is 4

Can somebody please confirm the following. E.g

Select avg(count) From ( Select count(*) as count From Table1 Where product = A Group by client) as counts

The average of those values is 2.667. Maybe you're asking for something different? Are you asking for the most common value? — Isolated, Apr 07 '22 at 14:22
So i did get the counts of products for each client first. Say Inner select returned count of products 1,2,14,4,4 for each of the 6 clients. I would like to outer select to get common number of product a client may have which is 4. Thanks in advance. — Darlson, Apr 07 '22 at 14:33
Your question above as the number "4" written 3 times, hence the average of 2.667. Your comment just above this has the number "14" for one of those 4's. At any rate, provide data that can be minimally reproduced and you'll get an answer. — Isolated, Apr 07 '22 at 14:41
Sorry newbie in DB2 SQL, I guess the term average is wrong, so if inner select Return 1,2,1,4,4,4, i would like the outer select to be 4. Meaning most of client have 4 products. Would you confirm the SQL I have provided? — Darlson, Apr 07 '22 at 14:52
What would you like to get on the following set of counts: (1,1,2,2,4,4)? — Mark Barinstein, Apr 07 '22 at 15:04
1,2,1,4,4,4 - i want to get 4 which is the common number for 6 clients — Darlson, Apr 07 '22 at 15:05
The question was about this: what if you have non-unique greatest number of products like in my example? Do you want to get all these greatest numbers or some arbitrary one of them? — Mark Barinstein, Apr 07 '22 at 15:20

Isolated · Answer 1 · 2022-04-07T15:24:28.310

Having sample data is important to getting assistance. It's still difficult to determine how your data looks. Let's assume it looks like this:

    create table table1 (
      client varchar(10), 
      product varchar(10)
     );
     
    insert into table1 values
    ('xxx', 'A'),
    ('bbb', 'A'), 
    ('bbb', 'A'), 
    ('ccc', 'A'), 
    ('ddd', 'A'),
    ('ddd', 'A'),
    ('ddd', 'A'),
    ('ddd', 'A'),
    ('tt', 'A'), 
    ('tt', 'A'), 
    ('tt', 'A'), 
    ('tt', 'A'), 
    ('bdad', 'A'), 
    ('bdad', 'A'), 
    ('bdad', 'A'), 
    ('bdad', 'A');

I don't have access to a DB2 database, but this query works for most dbms types. You may need to tweak to fit DB2.

    select purchased as most_common_value
    from (
      select client, count(*) as purchased
      from table1
      where product = 'A'
      group by client
      )z
    group by purchased
    order by count(client) desc
    limit 1

Output of query is:

    most_common_value
    4

Need to get Average count

1 Answers1