CODEWITHSUNDEEP
mysql
Amar Mond
Amar Mond

Reputation: 195

mySQL Aggregate Functions With Inner Join

I have the following tables:

CREATE TABLE `funds_balance` (
  `idBUSINESS` int(11) NOT NULL,
  `PREMIUM_POSITIONS_CREDIT` decimal(18,2) NOT NULL DEFAULT '0.00',
  PRIMARY KEY (`idBUSINESS`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `elevated_business_queue` (
  `idBUSINESS` int(11) NOT NULL,
  `KEYWORD_TEXT` varchar(200) NOT NULL,
  `CITY` varchar(50) NOT NULL,
  `BID_AMOUNT` decimal(18,2) NOT NULL,
  `NO_OF_DAYS` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

I am trying to select businesses from the elevated_business_queue table that have enough funds to cover all their bids from the funds_balance table. I came up with the following query:

SELECT ebq.idBUSINESS
     , KEYWORD_TEXT
     , CITY
     , BID_AMOUNT 
  FROM elevated_business_queue ebq
  JOIN funds_balance fb 
    ON fb.idBUSINESS = ebq.idBUSINESS
 GROUP 
    BY idBUSINESS
HAVING PREMIUM_POSITIONS_CREDIT >= (SUM(ebq.BID_AMOUNT) + (ROUND((12.36/100)*SUM(ebq.BID_AMOUNT)), 2));

but it does not work. I get the error Unknown column PREMIUM_POSITIONS_CREDIT. Why?

Upvotes: 0

Views: 279

Answers (2)

Marc B
Marc B

Reputation: 360642

HAVING clauses filter at the "end" of the query sequence, and can only work on fields which are actually selected in the query. Since you haven't included PREMIUM_POSTIIONS_CREDIT in your field list, it doesn't exist in the result set, and therefore HAVING can't filter on it.

Simply do

SELECT PREMIUM_POSITIONS_CREDIT, rest,of,the,fields

and it'll start working.

e.g.

mysql> CREATE TABLE foo (int x, int y);
Query OK, 0 rows affected (0.11 sec)

mysql> INSERT INTO foo (x,y) VALUES (1,1);
Query OK, 1 row affected (0.05 sec)
Records: 1 Duplicates: 0  Warnings: 0

mysql> SELECT x FROM foo HAVING y=1
ERROR 1054 (42S22): Unknown column 'y' in 'having clause'

mysql> select x,y from foo having y=1;
+------+------+
| x    | y    |
+------+------+
|    1 |    1 |
+------+------+
1 row in set (0.00 sec)

Upvotes: 2

Taj
Taj

Reputation: 1728

if you are using PREMIUM_POSITIONS_CREDIT in having clause it must be selected in select statment

Upvotes: 2

Related Questions