Counting rows from table

Balen

I have a table and want to count the bill no less than 50 and greater than 50. The following two commands works perfectly. But is it possible to perform with a single query.

SELECT name,COUNT(bill_no) AS Old FROM table WHERE bill_no <=50 GROUP BY name ;

SELECT name,COUNT(bill_no) AS New FROM table WHERE bill_no >50 GROUP BY name ;

+-----------+------------+
| name | bill_no |
+-----------+------------+
| A | 10 |
| A | 12 |
| B | 20 |
| A | 13 |
| C | 30 |
| C | 31 |
| D | 40 |
| E | 80 |
| E | 81 |
| E | 82 |
| K | 89 |
| L | 85 |
| M | 97 |
| M | 85 |
+-----------+------------+

Appreciate for advice.
Balen..

mrhappiness

mysql> select * from count_bill;
+-------+---------+-----------+
| cb_id | cb_name | cb_billno |
+-------+---------+-----------+
|     1 | a       |        10 |
|     2 | b       |        10 |
|     3 | a       |        30 |
|     4 | c       |        60 |
|     5 | d       |        80 |
|     6 | a       |        95 |
+-------+---------+-----------+
6 rows in set (0.00 sec)

mysql> SELECT
    ->   cb_name,
    ->   SUM(IF(cb_billno <= 50, 1, 0)) billno_lt_50,
    ->   SUM(IF(cb_billno > 50, 1, 0)) billno_gt_50
    -> FROM count_bill
    -> GROUP BY cb_name;
+---------+--------------+--------------+
| cb_name | billno_lt_50 | billno_gt_50 |
+---------+--------------+--------------+
| a       |            2 |            1 |
| b       |            1 |            0 |
| c       |            0 |            1 |
| d       |            0 |            1 |
+---------+--------------+--------------+
4 rows in set (0.00 sec)

hth

Balen

thanks .. works fine