Count in Case Condition! - 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

Count in Case Condition!

Author

Topic

ewan
Starting Member

13 Posts

Posted - 2011-12-13 : 03:39:30

I have to summarize the product purchased. I have this code:

SELECT RestaurantMaster.RestaurantCode, RestaurantMaster.RestaurantName,
CASE ItemMaster.Category2 WHEN 'CHICKEN ALC'
THEN COUNT(ItemMaster.Category2) END AS 'CHICKEN ALC'
CASE ItemMaster.Category2 WHEN 'CHICKEN EVM'
THEN COUNT(ItemMaster.Category2) END AS 'CHICKEN EVM',
CASE ItemMaster.Category2 WHEN 'BURGER ALC'
THEN COUNT(ItemMaster.Category2) END AS 'BURGER ALC',
CASE ItemMaster.Category2 WHEN 'BURGER EVM'
THEN COUNT(ItemMaster.Category2) END AS 'BURGER EVM',
CASE ItemMaster.Category2 WHEN 'BREAFAST ALC'
THEN COUNT(ItemMaster.Category2) END AS 'BREAKFAST ALC',
CASE ItemMaster.Category2 WHEN 'DESSERTS'
THEN COUNT(ItemMaster.Category2) END AS 'DESSERTS',
CASE ItemMaster.Category2 WHEN 'FRIES'
THEN COUNT(ItemMaster.Category2) END AS 'FRIES',
CASE ItemMaster.Category2 WHEN 'EVRDY_MCSAVERS'
THEN COUNT(ItemMaster.Category2) END AS 'EVRDY_MCSAVERS',
CASE ItemMaster.Category2 WHEN 'HAPPY MEAL'
THEN COUNT(ItemMaster.Category2) END AS 'HAPPY MEAL',
CASE ItemMaster.Category2 WHEN 'BEVERAGES'
THEN COUNT(ItemMaster.Category2) END AS 'BEVERAGES',
CASE ItemMaster.Category2 WHEN 'BREAKFAST BA P50'
THEN COUNT(ItemMaster.Category2) END AS 'BREAKFAST BA P50',
CASE ItemMaster.Category2 WHEN 'BREAKFAST BA'
THEN COUNT(ItemMaster.Category2) END AS 'BREAKFAST BA',
CASE ItemMaster.Category2 WHEN 'MCSAVERS P50'
THEN COUNT(ItemMaster.Category2) END AS 'MCSAVERS P50',
CASE ItemMaster.Category2 WHEN 'EXTRAS'
THEN COUNT(ItemMaster.Category2) END AS 'EXTRAS',
CASE ItemMaster.Category2 WHEN 'UPSIZE'
THEN COUNT(ItemMaster.Category2) END AS 'UPSIZE'

FROM OrdersHistory
JOIN RestaurantMaster
ON OrdersHistory.RestaurantID = RestaurantMaster.PKID
JOIN OrderItemsHistory
ON OrdersHistory.PKID = OrderItemsHistory.OrderFKID
JOIN ItemMaster
ON OrderItemsHistory.ItemFKID = ItemMaster.PKID

WHERE
YEAR(OrdersHistory.OrderDate) = 2011
AND month(OrdersHistory.OrderDate) = 10
--AND day(OrdersHistory.OrderDate) = 11
--AND (DATEPART(HOUR,OrdersHistory.OrderDate) >= 0
--AND DATEPART(HOUR,OrdersHistory.OrderDate) < 6)
--AND OrdersHistory.StatusFKID = 2

--FROM OrderItemsHistory
--JOIN OrdersHistory
--ON OrderItems.OrderFKID = OrdersHistory.PKID

GROUP BY RestaurantMaster.RestaurantCode, RestaurantMaster.RestaurantName, ItemMaster.Category2;
--ORDER BY RestaurantMaster.RestaurantCode, RestaurantMaster.RestaurantName

I got a result where its not in one row.
what is wrong with my code?

jeneca

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-13 : 04:03:16

as i suggested earlier you need to put COUNT() outside of CASE WHEN...
Also i dont think you need ItemMaster.Category2 in GROUP BY.so i think you can remove it

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