CODEWITHSUNDEEP
sqlmysql
Nanang Rustianto
Nanang Rustianto

Reputation: 11

how to make this mysql sub query work

SELECT c.noreg AS noreg, count( * ) AS jumlah
FROM kamar a, reservasi b, register c, identitas_psn d, sub_unit_kamar e
WHERE a.idkamar = b.kamar
AND b.noreg = c.id_reg_lengkap
AND c.norm = d.norm
AND c.idunit = e.id_subunit
AND c.noreg NOT
IN (

SELECT noreg
FROM register_keluar
)
AND (
c.idunit LIKE '20%'
)
AND jumlah > 1
GROUP BY noreg
ORDER BY jumlah DESC

// error is : AND jumlah > 1

Upvotes: 0

Views: 154

Answers (3)

all_by_grace
all_by_grace

Reputation: 2345

When you are combining the selection of any column and an aggregate alias (in your example: COUNT(*) as jumlah) and then you intend to use this aggregate alias (jumlah) somewhere in your query, with a GROUP BY condition, you need to use a HAVING condition after that corresponding GROUP BY.

Upvotes: 0

mechanical_meat
mechanical_meat

Reputation: 169464

Move jumlah > 1 into a HAVING clause, e.g.:

...
GROUP BY ...
HAVING jumlah > 1
ORDER BY ...

http://en.wikipedia.org/wiki/Having_(SQL):

A HAVING clause in SQL specifies that an SQL SELECT statement should only return rows where aggregate values meet the specified conditions.

Upvotes: 2

craigmoliver
craigmoliver

Reputation: 6562

try:

SELECT c.noreg AS noreg, count( * ) AS jumlah
FROM kamar a, reservasi b, register c, identitas_psn d, sub_unit_kamar e
WHERE a.idkamar = b.kamar
AND b.noreg = c.id_reg_lengkap
AND c.norm = d.norm
AND c.idunit = e.id_subunit
AND c.noreg NOT
IN ( SELECT noreg FROM register_keluar AND c.idunit LIKE '20%') 
AND jumlah > 1
GROUP BY noreg
ORDER BY jumlah DESC

Upvotes: 0

Related Questions