whoiskatrin whoiskatrin - 5 months ago 12
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