Variables in select query gives wrong value

March 26, 2022

I have 2 example tables like this :

When i try this query :

select @KOLI := count(barang_transaksi.sml) as 'KOLI', 
    @BERAT := sum(barang_transaksi.berat) as 'BERAT', 
    transaksi.harga as 'HARGA DASAR', 
    floor(@KOLI * transaksi.harga) as 'TOTAL KOLI', 
    floor(@BERAT * transaksi.harga) as 'TOTAL BERAT' 
from transaksi, barang_transaksi 
where barang_transaksi.sml = transaksi.sml 
group by barang_transaksi.sml;

and this query :

select @KOLI := count(b.sml) as 'KOLI', 
    @BERAT := sum(b.berat) as 'BERAT', 
    t.harga as 'HARGA DASAR', 
    floor(@KOLI * t.harga) as 'TOTAL KOLI', 
    floor(@BERAT * t.harga) as 'TOTAL BERAT' 
from transaksi t 
join barang_transaksi b on t.sml = b.sml 
group by t.sml;

both query give me wrong result like this :

the problem in column TOTAL KOLI and TOTAL BERAT, what i expected is like this :

in this example I use simple math for @KOLI and @BERAT but in my real work i use more complicated than that, that’s why I need to keep those variables so i don’t have to write that over and over again in my each select list columns (or maybe there is some other technique i can use you can suggest to me.)

I use MySql 5.6

Thanks in advance, sorry for my bad English and sorry if i made some mistake in this thread, this is my first time 🙂

>Solution :

Move the computation into a subquery rather than using variables.

SELECT koli, berat, harga AS `harga dasar`, FLOOR(koli * harga) AS total_koli, FLOOR(berat * harga) AS total_berat
FROM (
    SELECT COUNT(*) AS koli, SUM(b.berat) AS berat, harga
    FROM transaksi t 
    join barang_transaksi b on t.sml = b.sml 
    group by t.sml) AS x