mo Posted May 11, 2009 Share Posted May 11, 2009 I am trying to create a view which will hold the order status counts from my order table. My order table (store_order) has a field called order_status and order_date and this field can contains the following values (NEW, DELIVERED, COMPLETE, ON HOLD). I want my view to display counts from store_order.order_status for each possible status value where order_date = today. I tried the following but this does not work. I am no expert. CREATE OR REPLACE VIEW `order_stats_v` AS SELECT COUNT( distinct `order_status` ) AS open_orders FROM `store_order` WHERE `od_status`= 'NEW', SELECT COUNT( distinct `order_status` ) AS delivered_orders FROM `store_order` WHERE `od_status`= 'COMPLETE'), etc....... Link to comment https://forums.phpfreaks.com/topic/157693-query-for-multiple-counts-on-one-field/ Share on other sites More sharing options...
fenway Posted May 11, 2009 Share Posted May 11, 2009 What does "not work" mean? Error? Unexpected output? Please clarify. Link to comment https://forums.phpfreaks.com/topic/157693-query-for-multiple-counts-on-one-field/#findComment-831616 Share on other sites More sharing options...
mo Posted May 11, 2009 Author Share Posted May 11, 2009 What does "not work" mean? Error? Unexpected output? Please clarify. Error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' SELECT COUNT( distinct `order_status` ) AS delivered_orders FROM `store_order` WHERE' at line 2 Link to comment https://forums.phpfreaks.com/topic/157693-query-for-multiple-counts-on-one-field/#findComment-831620 Share on other sites More sharing options...
fenway Posted May 11, 2009 Share Posted May 11, 2009 Which mysql version? Link to comment https://forums.phpfreaks.com/topic/157693-query-for-multiple-counts-on-one-field/#findComment-831625 Share on other sites More sharing options...
mo Posted May 11, 2009 Author Share Posted May 11, 2009 Which mysql version? version 5.0.67.d7 Link to comment https://forums.phpfreaks.com/topic/157693-query-for-multiple-counts-on-one-field/#findComment-831626 Share on other sites More sharing options...
mo Posted May 12, 2009 Author Share Posted May 12, 2009 Any thoughts anyone? Link to comment https://forums.phpfreaks.com/topic/157693-query-for-multiple-counts-on-one-field/#findComment-832331 Share on other sites More sharing options...
kickstart Posted May 12, 2009 Share Posted May 12, 2009 Hi Have you got the full statement? You appear to have an extra closing bracket in what you have put up there, which suggests there is more to it. All the best Keith Link to comment https://forums.phpfreaks.com/topic/157693-query-for-multiple-counts-on-one-field/#findComment-832348 Share on other sites More sharing options...
mo Posted May 12, 2009 Author Share Posted May 12, 2009 Hi Have you got the full statement? You appear to have an extra closing bracket in what you have put up there, which suggests there is more to it. All the best Keith I would post my query but it does not work at all and I do not think I am building it correctly. I just want to create a view do the following so I do not have to build 5 seperate queries in my PHP code. Table store_orders: Order | Status ---------------- 1 | NEW 2 | NEW 3 | COMPLETE 4 | ON HOLD 5 | NEW 6 | NEW 7 | ON HOLD 8 | COMPLETE 9 | CANCELLED 10 | PROCESSING My view needs to look like the following: order_count_view NEW | PROCESSING | ON HOLD | CANCELLED | COMPLETE ------------------------------------------------------- 4 | 1 | 2 | 1 | 2 Link to comment https://forums.phpfreaks.com/topic/157693-query-for-multiple-counts-on-one-field/#findComment-832380 Share on other sites More sharing options...
kickstart Posted May 12, 2009 Share Posted May 12, 2009 Hi This works, but there must be a better way of doing it. Seems fairly pointless as a view with all counts on one line. I have changed the column names to avoid MySQL reserved words (ie, having a column called order is going to be a pain in future). CREATE OR REPLACE VIEW order_count_view (NewCnt, ProcessingCnt, OnHoldCnt, CancelledCnt, CompleteCnt) AS SELECT (SELECT COUNT(OrderNo) FROM store_orders WHERE OrderStatus = 'NEW'), (SELECT COUNT(OrderNo) FROM store_orders WHERE OrderStatus = 'PROCESSING'), (SELECT COUNT(OrderNo) FROM store_orders WHERE OrderStatus = 'ON HOLD'), (SELECT COUNT(OrderNo) FROM store_orders WHERE OrderStatus = 'CANCELLED'), (SELECT COUNT(OrderNo) FROM store_orders WHERE OrderStatus = 'COMPLETE') You could just get a similar result from a statement of :- SELECT OrderStatus, COUNT( OrderNo ) FROM store_orders GROUP BY OrderStatus LIMIT 0 , 30 All the best Keith Link to comment https://forums.phpfreaks.com/topic/157693-query-for-multiple-counts-on-one-field/#findComment-832441 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.