error in my mysql query with a limit condition

Question

i have 3 tables customers, times and sales i want to find out all the customers income yearly condition is that customers with no children and income must be greater than a limit we are set my table structure customers

CREATE TABLE `customers` (
  `customer_id` int(11) DEFAULT NULL,
  `account_num` double DEFAULT NULL,
  `lname` varchar(50) DEFAULT NULL,
  `fname` varchar(50) DEFAULT NULL,
  `mi` varchar(50) DEFAULT NULL,
  `address1` varchar(50) DEFAULT NULL,
  `address2` varchar(50) DEFAULT NULL,
  `address3` varchar(50) DEFAULT NULL,
  `address4` varchar(50) DEFAULT NULL,
  `postal_code` varchar(50) DEFAULT NULL,
  `region_id` int(11) DEFAULT NULL,
  `phone1` varchar(50) DEFAULT NULL,
  `phone2` varchar(50) DEFAULT NULL,
  `birthdate` datetime DEFAULT NULL,
  `marital_status` varchar(50) DEFAULT NULL,
  `yearly_income` varchar(50) DEFAULT NULL,
  `gender` varchar(50) DEFAULT NULL,
  `total_children` smallint(6) DEFAULT NULL,
  `num_children_at_home` smallint(6) DEFAULT NULL,
  `education` varchar(50) DEFAULT NULL,
  `member_card` varchar(50) DEFAULT NULL,
  `occupation` varchar(50) DEFAULT NULL,
  `houseowner` varchar(50) DEFAULT NULL,
  `num_cars_owned` smallint(6) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

sales

CREATE TABLE `sales` (
  `product_id` int(11) DEFAULT NULL,
  `time_id` int(11) DEFAULT NULL,
  `customer_id` int(11) DEFAULT NULL,
  `store_id` int(11) DEFAULT NULL,
  `store_sales` float DEFAULT NULL,
  `store_cost` float DEFAULT NULL,
  `unit_sales` double DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

times

CREATE TABLE `times` (
  `time_id` int(11) DEFAULT NULL,
  `the_date` datetime DEFAULT NULL,
  `the_day` varchar(50) DEFAULT NULL,
  `the_month` varchar(50) DEFAULT NULL,
  `the_year` smallint(6) DEFAULT NULL,
  `day_of_month` smallint(6) DEFAULT NULL,
  `month_of_year` smallint(6) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

MY question is :-Find the list of the customers with no child and an yearly_income greater that a limit given by the user when running the query.

MY query is

SET @limit=50;
SELECT customers.`fname`, customers.`lname` ,ROUND(SUM(sales.store_sales)) as income,times.the_year
 FROM `sales` 
 LEFT JOIN times
 ON sales.time_id=times.time_id
 LEFT JOIN customers 
ON customers.customer_id=sales.customer_id 
WHERE income>@limit AND `total_children`=0
GROUP BY sales.customer_id,times.the_year

am getting this error:#1054 - Unknown column 'income' in 'where clause'

I've removed sql server tag, as you use mysql. Please don't tag products not involved. — HoneyBadger, Mar 20 '18 at 09:13
income is calculated as ROUND(SUM(sales.store_sales)) as income i want this in that where condition — ANGELA, Mar 20 '18 at 09:16
Possible duplicate of [Can you use an alias in the WHERE clause in mysql?](https://stackoverflow.com/questions/200200/can-you-use-an-alias-in-the-where-clause-in-mysql) — lolbas, Mar 20 '18 at 09:20
`yearly_income` is a column in `customers`, I doubt it's related to the sum of sales. — dnoeth, Mar 20 '18 at 09:21
Wow, `total_children` is a `smallint(6)`, must be sperm donors :-) — dnoeth, Mar 20 '18 at 09:24

Tim Biegeleisen · Accepted Answer · 2018-03-20T09:22:20.917

The quantity you aliased as income is an aggregate, and therefore it does not make sense to refer to it in the WHERE clause. Move this WHERE logic to a HAVING clause:

SET @limit=50;
SELECT
    c.fname,
    c.lname,
    ROUND(SUM(s.store_sales)) AS income,
    t.the_year
FROM sales s 
LEFT JOIN times t
    ON s.time_id = t.time_id
LEFT JOIN customers c
    ON c.customer_id = s.customer_id 
WHERE
    total_children = 0
GROUP BY
    c.customer_id,
    t.the_year 
HAVING
    ROUND(SUM(s.store_sales)) > @limit;

Note that technically we could have used the alias in the HAVING clause:

HAVING income > @limit;

But this would not be portable to most other databases. Also, I introduced aliases into the query, which make it easier to read.

score 1 · Answer 2 · edited Mar 20 '18 at 14:12

1

For aggreagte columns not present in the original table you should use having clause instead of where

SET @limit=50;
SELECT customers.`fname`, customers.`lname` ,ROUND(SUM(sales.store_sales)) as income,times.the_year
 FROM `sales` 
 LEFT JOIN times
 ON sales.time_id=times.time_id
 LEFT JOIN customers 
ON customers.customer_id=sales.customer_id 
WHERE `total_children`=0
GROUP BY customers.customer_id,times.the_year 
having income>@limit

edited Mar 20 '18 at 14:12

Tim Biegeleisen

502,043
27
286
360

answered Mar 20 '18 at 09:16

Alexey

2,388
1
16
32

It will not work. Because the selected list is not in GROUP BY clause. – the.salman.a Mar 20 '18 at 09:20
@the.salman.a Actually it will, MySQL allows using aliases given to aggregated columns in `having` clause (compared for example to Oracle who doesn't allow it) – Alexey Mar 20 '18 at 09:23
That part is okay, I am talking about selected list. It'll give this `SELECT list is not in GROUP BY clause and contains nonaggregated column 'image_processing.customers.fname' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by` – the.salman.a Mar 20 '18 at 09:31
1

@the.salman.a As you can see from the error you get it's not allowed in SQL mode you've set (or which was set by default in your session). Changing SQL mode will solve the problem as MySQL allows non-aggregate columns in select list though started discouraging it's usage. – Alexey Mar 20 '18 at 09:40
yeah. That's also there. You got it. – the.salman.a Mar 20 '18 at 09:41
@the.salman.a Actually, grouping by `customers.customer_id`, assuming it's the primary key of that table, means that we may select _any_ other column in that table because they are functionally dependent on that primary key. So the only full group by error is not applicable here. – Tim Biegeleisen Mar 20 '18 at 14:03
@TimBiegeleisen yep I got it. That Mode was my issue. – the.salman.a Mar 20 '18 at 14:05
No...I'm saying that his query will work _in any mode_. If we group by a primary key in MySQL then we may always select any other column in that table. – Tim Biegeleisen Mar 20 '18 at 14:06

score 0 · Answer 3 · answered Mar 20 '18 at 09:15

income is alias name,so can't use that in where condition.

SET @limit=50;
SELECT * FROM
(
SELECT customers.`fname`, customers.`lname` ,ROUND(SUM(sales.store_sales)) 
       as income,times.the_year
FROM `sales` LEFT JOIN times ON sales.time_id=times.time_id
             LEFT JOIN customers ON customers.customer_id=sales.customer_id 
WHERE  `total_children`=0
GROUP BY sales.customer_id,times.the_year 
)t WHERE income>@limit

score 0 · Answer 4 · answered Mar 20 '18 at 09:26

0

Find the list of the customers with no child and an yearly_income greater that a limit given by the user when running the query.

Are you sure you don't want a simple query like this?

SELECT *
FROM customers AS c 
WHERE yearly_income > @limit  -- but why is yearly_income a VarChar(50)?
AND total_children=0

answered Mar 20 '18 at 09:26

dnoeth

59,503
4
39
56

there is no yearly income field – ANGELA Mar 20 '18 at 09:30
Of course there is, at least in the `CREATE TABLE customers` you posted :-) – dnoeth Mar 20 '18 at 09:44
,sorry yes.so how we find it? yearly income field is a varchar field with data like $30K - $50K – ANGELA Mar 20 '18 at 09:52
There's https://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_substring-index, e.g. `substring_index( '$30K - $50K', '-', 1)` returns `'$30K`. – dnoeth Mar 20 '18 at 10:04
SELECT substring_index( yearly_income, '-', -1) FROM customers – ANGELA Mar 20 '18 at 10:11
@doneth,how remove $ and K SELECT substring_index( yearly_income, '-', -1) FROM customers – ANGELA Mar 20 '18 at 10:17
For me *greater than a limit* translates to the 1st value – dnoeth Mar 20 '18 at 10:17
@doneth please tell how extract upper limit without any symbols? – ANGELA Mar 20 '18 at 10:22
Play around with substring: `substring('$30K' from 2 for position('K' in '$30K')-2)` or trim: `trim(trailing 'K ' from trim(leading '$' from '$30K')` – dnoeth Mar 20 '18 at 10:24
how remove symbols – ANGELA Mar 20 '18 at 10:30
Which symbols, `$`? – dnoeth Mar 20 '18 at 10:36
I wrote that 2 hours ago: Play around with substring: `substring('$30K' from 2 for position('K' in '$30K')-2)` or trim: `trim(trailing 'K ' from trim(leading '$' from '$30K')` – dnoeth Mar 20 '18 at 13:11

error in my mysql query with a limit condition

4 Answers4