ERROR FOUND! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

ERROR FOUND!

Author

Topic

ewan
Starting Member

13 Posts

Posted - 2011-12-12 : 01:15:50

Hi, I'm doing a SQL Query. I have error like this:

Operand data type nvarchar is invalid for sum operator.

This is my code:

SELECT RestaurantMaster.RestaurantCode, RestaurantMaster.RestaurantName,
CASE WHEN ItemMaster.Category2 = 'CHICKEN ALC' THEN COUNT(itemMaster.Category2) END AS CHICKEN_ALC,
CASE WHEN ItemMaster.Category2 = 'CHICKEN EVM' THEN COUNT(itemMaster.Category2) END AS CHICKEN_EVM,
CASE WHEN ItemMaster.Category2 = 'BURGER ALC' THEN COUNT(itemMaster.Category2) END AS BURGER_ALC,
CASE WHEN ItemMaster.Category2 = 'BURGER EVM' THEN COUNT(itemMaster.Category2) END AS BURGER_EVM,
CASE WHEN ItemMaster.Category2 = 'BREAKFAST ALC' THEN COUNT(itemMaster.Category2) END AS BREAKFAST_ALC,
CASE WHEN ItemMaster.Category2 = 'BREAKFAST EVM' THEN COUNT(itemMaster.Category2) END AS BREAKFAST_EVM,
CASE WHEN ItemMaster.Category2 = 'DESSERTS' THEN COUNT(itemMaster.Category2) END AS DESSERTS,
CASE WHEN ItemMaster.Category2 = 'FRIES' THEN COUNT(itemMaster.Category2) END AS FRIES,
CASE WHEN ItemMaster.Category2 = 'EVRDY MCSAVERS' THEN COUNT(itemMaster.Category2) END AS EVRDY_MCSAVERS,
CASE WHEN ItemMaster.Category2 = 'HAPPY MEAL' THEN COUNT(itemMaster.Category2) END AS HAPPY_MEAL,
CASE WHEN ItemMaster.Category2 = 'BEVERAGES' THEN COUNT(itemMaster.Category2) END AS BEVERAGES,
CASE WHEN ItemMaster.Category2 = 'BREAKFAST BA P50' THEN COUNT(itemMaster.Category2) END AS BREAKFAST_BA_P50,
CASE WHEN ItemMaster.Category2 = 'BREAKFAST BA' THEN COUNT(itemMaster.Category2) END AS BREAKFAST_BA,
CASE WHEN ItemMaster.Category2 = 'MCSAVER P50' THEN COUNT(itemMaster.Category2) END AS MCSAVER_P50,
CASE WHEN ItemMaster.Category2 = 'EXTRAS' THEN COUNT(itemMaster.Category2) END AS EXTRAS,
CASE WHEN ItemMaster.Category2 = 'UPSIZE' THEN COUNT(itemMaster.Category2) END AS UPSIZE,
SUM(ItemMAster.Category2)
FROM OrdersHistory
INNER JOIN OrderItemsHistory
ON OrdersHistory.PKID = OrderItemsHistory.OrderFKID
INNER JOIN ItemMaster
ON OrderItemsHistory.ItemFKID = ItemMaster.PKID
INNER JOIN RestaurantMaster
ON OrdersHistory.RestaurantID = RestaurantMaster.PKID
WHERE YEAR(OrdersHistory.OrderDate) = 2008
AND MONTH(OrdersHistory.OrderDate) = 9
AND DAY(OrdersHistory.OrderDate) = 20
GROUP BY RestaurantMaster.RestaurantCode, RestaurantMaster.RestaurantName, OrdersHistory.OrderDate, ItemMaster.Category2
ORDER BY RestaurantCode;

I have to add the results in the case condition based on the RestaurantCode.

Need help thanks.

jeneca

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-12 : 01:18:48

whats data type of ItemMAster.Category2? if its varchar and contains non numeric data you cant apply SUM() over it

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-12 : 01:25:53

also query can be better written as


SELECT RestaurantMaster.RestaurantCode, RestaurantMaster.RestaurantName,
COUNT(CASE WHEN ItemMaster.Category2 = 'CHICKEN ALC' THEN itemMaster.Category2 END) AS CHICKEN_ALC,
COUNT(CASE WHEN ItemMaster.Category2 = 'CHICKEN EVM' THEN itemMaster.Category2 END) AS CHICKEN_EVM,
COUNT(CASE WHEN ItemMaster.Category2 = 'BURGER ALC' THEN itemMaster.Category2 END) AS BURGER_ALC,
COUNT(CASE WHEN ItemMaster.Category2 = 'BURGER EVM' THEN itemMaster.Category2 END) AS BURGER_EVM,
COUNT(CASE WHEN ItemMaster.Category2 = 'BREAKFAST ALC' THEN itemMaster.Category2 END) AS BREAKFAST_ALC,
COUNT(CASE WHEN ItemMaster.Category2 = 'BREAKFAST EVM' THEN itemMaster.Category2 END) AS BREAKFAST_EVM,
COUNT(CASE WHEN ItemMaster.Category2 = 'DESSERTS' THEN itemMaster.Category2 END) AS DESSERTS,
COUNT(CASE WHEN ItemMaster.Category2 = 'FRIES' THEN itemMaster.Category2 END) AS FRIES,
COUNT(CASE WHEN ItemMaster.Category2 = 'EVRDY MCSAVERS' THEN itemMaster.Category2 END) AS EVRDY_MCSAVERS,
COUNT(CASE WHEN ItemMaster.Category2 = 'HAPPY MEAL' THEN itemMaster.Category2 END) AS HAPPY_MEAL,
COUNT(CASE WHEN ItemMaster.Category2 = 'BEVERAGES' THEN itemMaster.Category2 END) AS BEVERAGES,
COUNT(CASE WHEN ItemMaster.Category2 = 'BREAKFAST BA P50' THEN itemMaster.Category2 END) AS BREAKFAST_BA_P50,
COUNT(CASE WHEN ItemMaster.Category2 = 'BREAKFAST BA' THEN itemMaster.Category2 END) AS BREAKFAST_BA,
COUNT(CASE WHEN ItemMaster.Category2 = 'MCSAVER P50' THEN itemMaster.Category2 END) AS MCSAVER_P50,
COUNT(CASE WHEN ItemMaster.Category2 = 'EXTRAS' THEN itemMaster.Category2 END) AS EXTRAS,
COUNT(CASE WHEN ItemMaster.Category2 = 'UPSIZE' THEN itemMaster.Category2 END) AS UPSIZE,
SUM(ItemMAster.Category2)
FROM OrdersHistory
INNER JOIN OrderItemsHistory
ON OrdersHistory.PKID = OrderItemsHistory.OrderFKID
INNER JOIN ItemMaster
ON OrderItemsHistory.ItemFKID = ItemMaster.PKID
INNER JOIN RestaurantMaster 
ON OrdersHistory.RestaurantID = RestaurantMaster.PKID
WHERE YEAR(OrdersHistory.OrderDate) = 2008
AND MONTH(OrdersHistory.OrderDate) = 9
AND DAY(OrdersHistory.OrderDate) = 20
GROUP BY RestaurantMaster.RestaurantCode, RestaurantMaster.RestaurantName, OrdersHistory.OrderDate, ItemMaster.Category2
ORDER BY RestaurantCode;

Also you're grouping by Catregory2 and then taking SUM() of it. is it intentional?
Perhaps you can explain what you're trying to get giving some sample data

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-12 : 01:28:03

one more small thing


WHERE YEAR(OrdersHistory.OrderDate) = 2008
AND MONTH(OrdersHistory.OrderDate) = 9
AND DAY(OrdersHistory.OrderDate) = 20

can be written as

WHERE OrdersHistory.OrderDate>=20080920
AND OrdersHistory.OrderDate<20080921

to make use of an index if you've one on OrderDate field

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ewan
Starting Member

13 Posts

Posted - 2011-12-12 : 03:47:07

Oooops. My mistake.

This is my code:

SELECT RestaurantMaster.RestaurantCode, RestaurantMaster.RestaurantName,
CASE WHEN ItemMaster.Category2 = 'CHICKEN ALC' THEN COUNT(itemMaster.Category2) END AS CHICKEN_ALC,
CASE WHEN ItemMaster.Category2 = 'CHICKEN EVM' THEN COUNT(itemMaster.Category2) END AS CHICKEN_EVM,
CASE WHEN ItemMaster.Category2 = 'BURGER ALC' THEN COUNT(itemMaster.Category2) END AS BURGER_ALC,
CASE WHEN ItemMaster.Category2 = 'BURGER EVM' THEN COUNT(itemMaster.Category2) END AS BURGER_EVM,
CASE WHEN ItemMaster.Category2 = 'BREAKFAST ALC' THEN COUNT(itemMaster.Category2) END AS BREAKFAST_ALC,
CASE WHEN ItemMaster.Category2 = 'BREAKFAST EVM' THEN COUNT(itemMaster.Category2) END AS BREAKFAST_EVM,
CASE WHEN ItemMaster.Category2 = 'DESSERTS' THEN COUNT(itemMaster.Category2) END AS DESSERTS,
CASE WHEN ItemMaster.Category2 = 'FRIES' THEN COUNT(itemMaster.Category2) END AS FRIES,
CASE WHEN ItemMaster.Category2 = 'EVRDY MCSAVERS' THEN COUNT(itemMaster.Category2) END AS EVRDY_MCSAVERS,
CASE WHEN ItemMaster.Category2 = 'HAPPY MEAL' THEN COUNT(itemMaster.Category2) END AS HAPPY_MEAL,
CASE WHEN ItemMaster.Category2 = 'BEVERAGES' THEN COUNT(itemMaster.Category2) END AS BEVERAGES,
CASE WHEN ItemMaster.Category2 = 'BREAKFAST BA P50' THEN COUNT(itemMaster.Category2) END AS BREAKFAST_BA_P50,
CASE WHEN ItemMaster.Category2 = 'BREAKFAST BA' THEN COUNT(itemMaster.Category2) END AS BREAKFAST_BA,
CASE WHEN ItemMaster.Category2 = 'MCSAVER P50' THEN COUNT(itemMaster.Category2) END AS MCSAVER_P50,
CASE WHEN ItemMaster.Category2 = 'EXTRAS' THEN COUNT(itemMaster.Category2) END AS EXTRAS,
CASE WHEN ItemMaster.Category2 = 'UPSIZE' THEN COUNT(itemMaster.Category2) END AS UPSIZE
FROM OrdersHistory
INNER JOIN OrderItemsHistory
ON OrdersHistory.PKID = OrderItemsHistory.OrderFKID
INNER JOIN ItemMaster
ON OrderItemsHistory.ItemFKID = ItemMaster.PKID
INNER JOIN RestaurantMaster
ON OrdersHistory.RestaurantID = RestaurantMaster.PKID
WHERE YEAR(OrdersHistory.OrderDate) = 2008
AND MONTH(OrdersHistory.OrderDate) = 9
--AND DAY(OrdersHistory.OrderDate) = 20
GROUP BY RestaurantMaster.RestaurantCode, RestaurantMaster.RestaurantName, ItemMaster.Category2
ORDER BY RestaurantMaster.RestaurantCode;

It run successfully but I have to only show the columns who have values.

and for the date it's a requirement so we can change the date range if ever there is someone who just wanted to see a result for a month.

it is a summary of purchased goods or product

jeneca

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-12 : 09:49:03

one thing is as i suggested please put CASE WHEN inside COUNT
for seeing result for a month just make group by as


...
GROUP BY DATEADD(mm,DATEDIFF(mm,0,OrdersHistory.OrderDate),0) ,RestaurantMaster.RestaurantCode, RestaurantMaster.RestaurantName, ItemMaster.Category2

this will give result split up by month and year wise

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ewan
Starting Member

13 Posts

Posted - 2011-12-13 : 03:42:02

Okay, I will try that. Thanks, :D

jeneca

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-13 : 04:12:09

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources