MySql - producing statistics

[nibbo]

I have a MySQL database for order processing and I would like to produce a statistics page for the owners web pages.

Basically I want to say how many orders exist and their status for each order type.

 

Something like:

 

Order TypeNumber at status 1Number at status 2Number at status 3

Annnnnnnnnnnn

Bnnnnnnnnnnnn

etc...nnnnnnnnnnnn

Totalnnnnnnnnnnnn

 

Obviously I can retreive each order and accumulate the statistic in a loop but I feel there must be a better way.

 

Anyone know of the best (and most efficient) way to produce this kind of data?

 

Thanks in advance.

 

 

 

 

 

[fenway]

Assuming "status1" can be evaluated as some sort of expression/query, you can use subqueries to extract this at once....

[aschk]

Another pivot table (yawn....zzz) and yes you can produce this information, but we can't help you any further unless you provide a table layout + sample data + sample output.

[nibbo]

Thanks both;

I looked into subqueries and they did not seem to offer anything from an efficiency point and probably look more confusing than accumulating things manually.

 

A 'pivot' table you say... I will have a look at these and see.

 

As for the sample data; it is a purchase order processing system with quotations also.

So we have basically two order types P=Purchase Orders and Q=Quotes.

These can each have three different settings; 0=new; 1=partially complete; 2=complete.

I want to output a table showing:

 

Order Type      New            Partial            Complete

 

Purchases            n                  n                    n

Quotes                n                  n                    n

 

As I said I can happily select all and accumulate manually but that just seems naff and I am sure there must be a cute way of doing it.

Thanks a lot.

 

 

[fenway]

A "pivot table" is still a bunch of subqueries... and efficiency isn't really an option here, you have to run the queries on way or another.

[soycharliente]

<?php
$sql = "SELECT `order_type`,
COUNT(SELECT `status` FROM `table` WHERE `status`='0' AND `order_type`='P') AS `new_p`,
COUNT(SELECT `status` FROM `table` WHERE `status`='1' AND `order_type`='P') AS `part_p`,
COUNT(SELECT `status` FROM `table` WHERE `status`='2' AND `order_type`='P') AS `comp_p`,
COUNT(SELECT `status` FROM `table` WHERE `status`='0' AND `order_type`='Q') AS `new_q`,
COUNT(SELECT `status` FROM `table` WHERE `status`='1' AND `order_type`='Q') AS `part_q`,
COUNT(SELECT `status` FROM `table` WHERE `status`='2' AND `order_type`='Q') AS `comp_q`
FROM `table`";
?>

 

Something like that? I don't know even know if that will work.

I'm SURE there is a better way.

[fenway]

Yes, that wil will work... you could also try and get away with COUNT() with GROUP BY status, order_type -- that way, at least all the counting will be from a single query.

[soycharliente]

I didn't know if you could get all the sums for each different status with GROUP BY since it would have to filter out the same field for three different values.

[soycharliente]

So like?

 

<?php
$sql = "SELECT `order_type`,
(SELECT `status` FROM `table` WHERE `status`='0' AND `order_type`='P' GROUP BY `order_type`) AS `new_p`,
(SELECT `status` FROM `table` WHERE `status`='1' AND `order_type`='P' GROUP BY `order_type`) AS `part_p`,
(SELECT `status` FROM `table` WHERE `status`='2' AND `order_type`='P' GROUP BY `order_type`) AS `comp_p`,
(SELECT `status` FROM `table` WHERE `status`='0' AND `order_type`='Q' GROUP BY `order_type`) AS `new_q`,
(SELECT `status` FROM `table` WHERE `status`='1' AND `order_type`='Q' GROUP BY `order_type`) AS `part_q`,
(SELECT `status` FROM `table` WHERE `status`='2' AND `order_type`='Q' GROUP BY `order_type`) AS `comp_q`
FROM `table`";
?>

[nibbo]

Thanks again for all contributions but I still can't see that any of these are any better that just accumulating manually.

I have done:

 

$sql = "SELECT OrderType, OrderStatus FROM orderheader";

$Orders = mysql_query($sql)

or die("blah blah");

 

while ($Order = mysql_fetch_object($Orders)) {

if ($Order->OrderType == "P") {$NumOrders[$Order->OrderStatus]++; } else {$NumQuotes[$Order->OrderStatus]++; }

}

 

Anyone see a problem with doing it this way or think of anything better?

TIA

 

 

[soycharliente]

Because MySQL is faster than PHP if I'm not mistaken. You will already have everything you need in a field that you can reference much easier.

[fenway]

So like?

 

<?php
$sql = "SELECT `order_type`,
(SELECT `status` FROM `table` WHERE `status`='0' AND `order_type`='P' GROUP BY `order_type`) AS `new_p`,
(SELECT `status` FROM `table` WHERE `status`='1' AND `order_type`='P' GROUP BY `order_type`) AS `part_p`,
(SELECT `status` FROM `table` WHERE `status`='2' AND `order_type`='P' GROUP BY `order_type`) AS `comp_p`,
(SELECT `status` FROM `table` WHERE `status`='0' AND `order_type`='Q' GROUP BY `order_type`) AS `new_q`,
(SELECT `status` FROM `table` WHERE `status`='1' AND `order_type`='Q' GROUP BY `order_type`) AS `part_q`,
(SELECT `status` FROM `table` WHERE `status`='2' AND `order_type`='Q' GROUP BY `order_type`) AS `comp_q`
FROM `table`";
?>

No, I meant SELECT status, order_type, count(*) from `table` group by status, order_type.