whoiskatrin whoiskatrin - 3 months ago 6
SQL Question

How to get an average number from already counted value

select aa.AccNumber, aa.AccName, count(ProductID) as Products
from vProducts pr
left join vAllAccounts aa with (nolock)
on aa.AccountID = pr.AccountID
group by aa.AccNumber, aa.AccID, aa.AccName
order by Products desc


Hi, here is my query.
I counted total numbers of products per account, but now I need to count average number of products per account using previous query.
Help me, please. I have never done this before.

Answer
select avg(sales.Products) from
(
    select aa.AccNumber, aa.AccName, count(ProductID) as Products
    from vProducts pr
    left join vAllAccounts aa with (nolock) 
    on aa.AccountID = pr.AccountID
    group by  aa.AccNumber, aa.AccID, aa.AccName
) sales