# [SOLVED] Variables in select query gives wrong value

## Issue

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
``````