CODEWITHSUNDEEP
mysql
18Man
18Man

Reputation: 572

count average for selected data mysql

i have data user called order_star_member with this detail, user_id as the id user, createdAt as the transaction begin, total_price_star_member as the amount of the transaction for each users.

CREATE TABLE order_star_member 
 ( users_id INT
 , createdAt DATE
 , total_price_star_member DECIMAL(10,2)
 );

INSERT INTO order_star_member  VALUES
(12,'2019-01-01',100000),
(12,'2019-01-10',100000),
(12,'2019-01-20',100000),
(12,'2019-02-10',100000),
(12,'2019-02-15',300000),
(12,'2019-02-21',500000),
(13,'2019-01-02',900000),
(13,'2019-01-11',300000),
(13,'2019-01-18',400000),
(13,'2019-02-06',100000),
(13,'2019-02-08',900000),
(13,'2019-02-14',400000),
(14,'2019-01-21',500000),
(14,'2019-01-23',200000),
(14,'2019-01-24',300000),
(14,'2019-02-08',100000),
(14,'2019-02-09',200000),
(14,'2019-02-14',100000),
(15, '2019-03-04',1000000),
(14, '2019-03-04', 300000),
(14, '2019-03-04', 350000),
(13, '2019-03-04', 400000),
(15, '2019-01-23', 620000),
(15, '2019-02-01', 650000),
(12, '2019-03-03', 750000),
(16, '2019-03-04', 650000),
(17, '2019-03-03', 670000),
(18, '2019-02-02', 450000),
(19, '2019-03-03', 750000);

and i separate the data users for transaction in january with the total transaction >= 600.000

select * from order_star_member where monthname(createdAt) = 'January'
having sum(total_price_star_member) >= 600000;

and the result was this 

+----------+-------------------------+
| users_id | total_price_star_member |
+----------+-------------------------+
|       13 |               1.600.000 |
|       14 |               1.000.000 |
|       15 |                 620.000 |
+----------+-------------------------+

i want to selected the users who have total amount of transaction in >= 600.000 in a month of january and the average of that data users transaction of it. i used this query

select avg(total_price_star_member) from 
(select users_Id, total_price_star_member 
from order_star_member  
where monthname(createdAt) = 'January'
group by users_id
having sum(total_price_star_member) >= 600000) a;

but why the result average was the overall of users instead it only counted average if the amount of total transaction of each users in january was >= 600.000

the average should be 1.073.000 but why i use that query why the result was 673.333

here's the fiddle http://sqlfiddle.com/#!9/c77f2/11

Upvotes: 0

Views: 43

Answers (1)

MrApnea
MrApnea

Reputation: 1946

The simplest answer would probably be something like the followin (using temporary tables). Because of a problem with MySql (not in MariaDB) with temporary tables we need to create two of them:

create temporary table tmpInfo1 as
select users_id, sum(total_price_star_member) userSum
from order_star_member 
where monthname(createdAt) = 'January'
group by users_id
having sum(total_price_star_member) >= 600000;

create temporary table tmpInfo2 as select * from tmpInfo1;

select  users_id, 
        userSum, 
        (select avg(userSum) from tmpInfo2)
from tmpInfo1;


drop table tmpInfo1;
drop table tmpInfo2;

Upvotes: 1

Related Questions