Query for multiple counts on one field

[mo]

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.......

[fenway]

What does "not work" mean? Error? Unexpected output? Please clarify.

[mo]

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

[fenway]

Which mysql version?

[mo]

Which mysql version?

 

version 5.0.67.d7

[mo]

Any thoughts anyone?

[kickstart]

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

[mo]

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

[kickstart]

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