CODEWITHSUNDEEP
mysql
su919
su919

Reputation: 97

How to get count greater than and less than average values group by name

I have a data set with name and their transaction ,how to get average and count of transactions grater than that average and less than that average..

Name    Transaction
John    12
John    34
John    45
John    66
John    32
chris   26
chris   54
chris   56
chris   99
chris   13
chris   4
kim     22
kim     34
kim     7
kim     11
kim     34

O/P will be

Name      Avg     Count_greater_than_Avg    Count_Less_than_Avg
John      37.8          2                           3
chris     42            3                           3
kim       21.6          3                           2

Thanks in advance..

Upvotes: 3

Views: 2221

Answers (2)

sagi
sagi

Reputation: 40481

This basically first add a column Your_Avg using a correlated query, and then wrap it with another select to select the count of the occurrences of times smaller then avg and times larger.

SELECT tt.name,tt.Your_Avg,
       count(CASE WHEN tt.Your_Avg > tt.Transaction then 1 end) as Greater_Then_Avg,
       count(CASE WHEN tt.Your_Avg > tt.Transaction then 1 end) as Smaller_Then_Avg
FROM(
    SELECT t.name,
           (SELECT avg(s.transaction) FROM YourTable s WHERE s.name = t.name) as Your_Avg,
           t.transaction
    FROM YourTable) tt
GROUP BY tt.name

Upvotes: 0

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

Try this:

SELECT t1.Name, t2.Aver,
       COUNT(CASE WHEN Transaction < Aver THEN 1 END) Count_Less_than_Avg,
       COUNT(CASE WHEN Transaction > Aver THEN 1 END) Count_greater_than_Avg
FROM mytable AS t1
JOIN (
  SELECT Name, AVG(Transaction * 1.0) AS Aver
  FROM mytable
  GROUP BY Name
) AS t2 ON t1.Name = t2.Name
GROUP By Name

You need a derived table in order to calculate the average value per Name. You can then JOIN the original table to this derived table and use conditional aggregation in order to calculate less than, greater than number of transactions.

Demo here

Upvotes: 2

Related Questions