CODEWITHSUNDEEP
mysqlsqlgroup-by
Nimalan Karthik
Nimalan Karthik

Reputation: 1

using an aggregate value along with non-agrregate values in SQL

I need to use the average value of the column 'sales' from a table called 'previous_target' and compare that value with the individual rows of the same column 'sales' of the same table . I get the required result when I disable the SQL mode from only_full_group_by. But i would like to know if there is a better way to write the code without disabling the full group by mode. Here is an example SQL query.

select 
f.id,t.sale as previous_target, 
case
when t.sale > 1.25*(avg(t.sale)) then round((avg(t.sale)*1.1),2)
when t.sale < 0.9*(avg(t.sale)) then round((avg(t.sale)*0.9),2)
else
t.sale 
end 
as current_target from 
details f 
inner join prev_target t on f.l_number=t.l_number
inner join time_details ft on ft.id=f.id

note:if i add the line 

group by f.id,f.l_number,t.sale

it just copies the same value onto the current_target column .

can anyone suggest a way to use the average of the sales column from the prev_target table and compare it with each row of the same table with the given conditions. I hope I conveyed my requirement without causing much confusion.

Upvotes: 0

Views: 49

Answers (2)

D Mayuri
D Mayuri

Reputation: 456

SELECT f.id, t.sale AS previous_target, 
CASE
    WHEN t.sale > 1.25*(a.sale) then round(((a.sale)*1.1),2)
    WHEN t.sale < 0.9*(a.sale) then round(((a.sale)*0.9),2)
ELSE
    t.sale 
END AS current_target 
FROM details f 
INNER JOIN prev_target t ON f.l_number = t.l_number
INNER JOIN 
(
    SELECT avg(t.sale) AS sale, t.l_number FROM prev_target t GROUP BY t.l_number
)AS a ON t.l_number = a.l_number
INNER JOIN time_details ft ON ft.id = f.id

Upvotes: 1

Amith Kumar
Amith Kumar

Reputation: 4870

Using OVER clause would be best recommended here for aggregation. I have created sample run for you (ofcourse different data and columns) but you will get the gist.

create table sales(amount int);
insert into sales values(5);
insert into sales values(10);
insert into sales values(15);
insert into sales values(20);
insert into sales values(10);

select * from sales;

select avg(amount) average from sales;

select amount, case when amount >= (avg(amount) over (PARTITION BY 1)) then 'Good' else 'Bad' end as type from sales;

Result >>

enter image description here

Upvotes: 0

Related Questions