Sql - Error with expression of non-boolean

Question

Can anyone help me find this issue?

This is my code

SELECT 
    MSC_Customer.cust_number, cust_name, 
    COUNT(ord_number) AS number_of_orders
FROM 
    MSC_Customer, MSC_Order
WHERE 
    MSC_Customer.cust_number = MSC_Order.cust_number
HAVING 
    MSC_Customer.cust_number
GROUP BY 
    cust_city LIKE 'Pennsylvania';

I get this error

Msg 4145, Level 15, State 1, Line 5
An expression of non-boolean type specified in a context where a condition is expected, near 'GROUP'.

I am trying to join the two tables, and use a COUNT aggregate and a GROUP BY clause in the SELECT statement

I have removed the MySQL tag, as the error message look like SQL Server. Please add some sample data to your question which demonstrates what you are trying to here. — Tim Biegeleisen
– Tim Biegeleisen, Commented Oct 28, 2018 at 2:13

Tim Biegeleisen · Accepted Answer · 2018-10-28 02:16:17Z

3

I'm going to suggest the following corrected query:

SELECT
    c.cust_number,
    c.cust_name,
    COUNT(ord_number) AS number_of_orders
FROM MSC_Customer c
INNER JOIN MSC_Order o
    ON c.MSC_Customer = o.cust_number
WHERE
    cust_city LIKE '%Pennsylvania%'    -- or maybe just cust_city = 'Pennsylvania'
GROUP BY
    c.cust_number,
    c.cust_name;

I am assuming that you want to aggregate by customer name/number. The check on the customer city would seem to belong in a WHERE clause, not in the GROUP BY clause. Of note, I rephrased your query to use an explicit inner join, instead of an old school implicit join. This is the preferred way of writing joins now.

answered Oct 28, 2018 at 2:16

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

SMor Over a year ago

One small correction - qualify the column in the where clause with the appropriate alias And perhaps add an order by clause with explanation about why.

Tim Biegeleisen Over a year ago

@SMor Fixing the alias assumes I know to which table the cust_city column belongs.

Dhana · Accepted Answer · 2018-10-28 02:16:17Z

0

Use condition in Where not Group By. Try below Script

SELECT MSC_Customer.cust_number, cust_name, count(ord_number) AS 
number_of_orders
FROM MSC_Customer
JOIN MSC_Order ON
    MSC_Customer.cust_number = MSC_Order.cust_number 
WHERE cust_city like '%Pennsylvania%'
GROUP BY cust_city;

answered Oct 28, 2018 at 2:16

Dhana

1,6584 gold badges24 silver badges40 bronze badges

Comments

Suraj Kumar · Accepted Answer · 2018-10-28 15:57:44Z

0

This type of error occurs generally when you write keyword which is used in expression or condition but you have not passed the value of the condition. For example -

select * from table where

this will give the same error as you have not passed here the value for where

You have missed to write the condition in having clause. You need to write the value with operator in having clause which may be one of the following.

HAVING 
    MSC_Customer.cust_number = '0120008024'
OR 
HAVING 
    MSC_Customer.cust_number <> '0120008024'
OR 
HAVING 
    MSC_Customer.cust_number like '%0120008024%'

You have to only specify the values in having clause you have missed as per your requirement.

edited Oct 28, 2018 at 15:57

answered Oct 28, 2018 at 15:44

Suraj Kumar

5,6748 gold badges24 silver badges45 bronze badges

Collectives™ on Stack Overflow

Sql - Error with expression of non-boolean

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related