0

i have a error on my query

this my query

SELECT a.id_stock, a.color, a.size, (SUM(IFNULL(a.barang_masuk,0)) - SUM(IFNULL(a.reject,0)) - IFNULL((
    SELECT SUM(IFNULL(qty,0)) AS SUMqty FROM transaksi WHERE transaksi.id_stock = a.id_stock GROUP BY id_stock
),0)) AS sold FROM stock a
WHERE a.no_barang='10' AND sold>0
GROUP BY a.no_barang, a.color, a.size
ORDER BY color, size ASC

and this the error

#1054 - Unknown column 'sold' in 'where clause'

have any suggest to settle this?? thanks

Hansen
  • 650
  • 1
  • 11
  • 32

1 Answers1

1

Custom aliases are not treated by where clause you need to use HAVING for this 

SELECT a.id_stock, a.color, a.size, (SUM(IFNULL(a.barang_masuk,0)) - SUM(IFNULL(a.reject,0)) - IFNULL((
    SELECT SUM(IFNULL(qty,0)) AS SUMqty FROM transaksi WHERE transaksi.id_stock = a.id_stock GROUP BY id_stock
),0)) AS sold FROM stock a
HAVING a.no_barang='10' AND sold>0
GROUP BY a.no_barang, a.color, a.size
ORDER BY color, size ASC

For the problem i guess you cannot use having before group by you can use subselect

SELECT q.* FROM (
    SELECT a.id_stock, a.color, a.size, (SUM(IFNULL(a.barang_masuk,0)) - SUM(IFNULL(a.reject,0)) - IFNULL((
        SELECT SUM(IFNULL(qty,0)) AS SUMqty FROM transaksi WHERE transaksi.id_stock = a.id_stock GROUP BY id_stock
    ),0)) AS sold FROM stock a

    HAVING a.no_barang='10' AND sold>0 
 /* you can use where and having both in same query like WHERE a.no_barang='10' HAVING sold>0 */

    ORDER BY color, size ASC ) q  GROUP BY q.no_barang, q.color, q.size

EDIT

SELECT a.id_stock, a.color, a.size, (SUM(IFNULL(a.barang_masuk,0)) - SUM(IFNULL(a.reject,0)) - IFNULL((
    SELECT SUM(IFNULL(qty,0)) AS SUMqty FROM transaksi WHERE transaksi.id_stock = a.id_stock GROUP BY id_stock
),0)) AS sold FROM stock a
WHERE a.no_barang='10' 
GROUP BY a.no_barang, a.color, a.size
HAVING sold>0
ORDER BY color, size ASC

Unknown Column In Where Clause

How to use GROUP BY after the Having clause Mysql

Community
  • 1
  • 1
M Khalid Junaid
  • 63,861
  • 10
  • 90
  • 118
  • i still found on error, i use the second query from your answer, the the error 1054 - Unknown colomn 'a.no_barang' in 'having clause' – Hansen Nov 15 '13 at 11:17
  • @Hans Have you read my comments *WHERE a.no_barang='10' HAVING sold>0* – M Khalid Junaid Nov 15 '13 at 11:18
  • after i try with second query but the result is just 1 row, the query is group are row as one row with sum(sold) – Hansen Nov 15 '13 at 11:28