CODEWITHSUNDEEP
sql-server
konna.papag
konna.papag

Reputation: 29

Find the average() of a count() in SQL Server 2012

I want to calculate the average of a resultset from count function. I used this query:

SELECT 
    CAST(AVG(purchase) AS NUMERIC (5,3))
FROM 
    (SELECT count(*) as purchase 
     FROM transactions
     GROUP BY payer_id) X

but I get as result the value "1". I use Excel to verify this result and it is supposed to be 1,635264484.

I also wanted to return both the count() list and the average of this list so I rewrite my query like this:

SELECT 
    tr1.payer_id, count(*) AS purchase
FROM 
    transactions AS tr1
GROUP BY 
    payer_id 

UNION ALL 

SELECT 'avg = ', AVG(X.purchase) 
FROM 
    (SELECT count(*) as purchase
     FROM transactions tr2
     GROUP BY tr2.payer_id) X

Now, the query runs for a while and then the following error message is shown:

Error converting data type varchar to numeric.

The column payer_id is numeric(38,0).

Any ideas?

Upvotes: 0

Views: 182

Answers (1)

Allan S. Hansen
Allan S. Hansen

Reputation: 4091

You cannot convert 'avg =' to numeric, which it tries due to your UNION, because your player_id is numeric.

If you cast payer_id to text, it should work:

SELECT 
    CAST(tr1.payer_id AS VARCHAR(255)), count(*) AS purchase
FROM 
    transactions AS tr1
GROUP BY 
    payer_id 

UNION ALL 

SELECT 'avg = ', AVG(X.purchase) 
FROM 
    (SELECT count(*) as purchase
     FROM transactions tr2
     GROUP BY tr2.payer_id) X

Upvotes: 1

Related Questions