Andras -
If you create an additional table which contains all possible values
for the order_status field, you can do this. Something like this:
create table orderstatus (
statusname varchar(25) );
insert into orderstatus (statusname) values ('pending'),
('processing'), ('shipped');
Next you have an orders table, with 2 pending, 1 processing, and no
shipped orders:
create table orders (
id int unsigned not null auto_increment primary key,
statusname varchar(25) );
insert into orders ( statusname ) values ('pending'), ('pending'),
('processing');
And finally a query to show you the correct values:
-> select os.statusname, count(o.statusname) as count
-> from orderstatus os
-> left join orders o using (statusname)
-> group by os.statusname;
+------------+-------+
| statusname | count |
+------------+-------+
| pending | 2 |
| processing | 1 |
| shipped | 0 |
+------------+-------+
3 rows in set (0.00 sec)
Dan
Hello,
I would like merge this 3 query into a single one...
SELECT COUNT(*) AS count FROM orders WHERE order_status = 'pending',
SELECT COUNT(*) AS count FROM orders WHERE order_status = 'processing',
SELECT COUNT(*) AS count FROM orders WHERE order_status = 'shipped',
I could do :
SELECT order_status, COUNT( * ) AS count FROM orders GROUP BY order_status
But sometimes all orders set to shipped and only returning 1 row,
but need all 3 rows with null values...
Thanks,
Andras Kende
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
