CODEWITHSUNDEEP
sqlsql-server
kiko
kiko

Reputation: 1

Sql - Error with expression of non-boolean

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

Upvotes: 0

Views: 701

Answers (3)

Suraj Kumar
Suraj Kumar

Reputation: 5643

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.

Upvotes: 0

Dhana
Dhana

Reputation: 1658

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;

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520898

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.

Upvotes: 3

Related Questions