mysql count multiple row where

Question

my question similar like this one MySQL COUNT() multiple columns.

but for me , how do i count the mutliple row:

report table:

id     t_pro      t_price
1.    voucher    20.00
2.    pro        13.00
3.    pro 1x     13.00
4.    pro 2x       13.00
5.    voucher    20.00
6.    pro 1x       13.00 

$db->query("SELECT COUNT(*) FROM (SELECT t_pro WHERE t_pro LIKE 'pro%' AS pro_tan, SELECT t_pro WHERE t_pro LIKE 'voucher%' AS voucher) WHERE date=CURDATE()  ");

the result something like:

voucher : 2
pro tan : 4

why are you using a select within a select? – Naftali Apr 27 '11 at 16:15 — Naftali, Apr 27 '11 at 16:15

score 3 · Answer 1 · answered Apr 27 '11 at 16:15

3

SELECT  CASE WHEN t_pro LIKE 'voucher%' THEN 'voucher' WHEN t_pro LIKE 'pro%' THEN 'pro tan' END AS pro,
        COUNT(*)
FROM    mytable
WHERE   t_pro LIKE 't_pro%' OR t_pro LIKE 'voucher%'
GROUP BY
        pro

answered Apr 27 '11 at 16:15

Quassnoi

413,100
91
616
614

1

@Quassnoi: thanks. how do i echoing out the result? `echo pro tan = $r['pro']` ?? – tonoslfx Apr 27 '11 at 16:21
im not using any framework. its custom code on my own. how do i echoing the result like `voucher: 2, pro tan: 4` – tonoslfx Apr 27 '11 at 16:28
@Quassnoi: sorry my bad :D.. its from $db = new Database() class – tonoslfx Apr 27 '11 at 16:30

score 2 · Accepted Answer · answered Apr 27 '11 at 16:16

2

You can use GROUP BY:

SELECT t_pro, COUNT(*) FROM report GROUP BY t_pro;

NOTE: this is if you want the count for each t_pro value. Otherwise, you'd need to modify the GROUP BY for custom t_pro value associations

answered Apr 27 '11 at 16:16

Jason McCreary

71,546
23
135
174

score 0 · Answer 3 · answered Apr 27 '11 at 16:17

0

select t_pro, count(1) from table group by t_pro;

answered Apr 27 '11 at 16:17

Liv

6,006
1
22
29

mysql count multiple row where

3 Answers3