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
– Naftali

2011-04-27 16:15:57 +00:00
Commented Apr 27, 2011 at 16:15 — Naftali
– Naftali, Commented Apr 27, 2011 at 16:15

Quassnoi · Accepted Answer · 2011-04-27 16:15:54Z

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, 2011 at 16:15

Quassnoi

427k94 gold badges628 silver badges623 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

tonoslfx Over a year ago

@Quassnoi: thanks. how do i echoing out the result? echo pro tan = $r['pro'] ??

tonoslfx Over a year ago

im not using any framework. its custom code on my own. how do i echoing the result like voucher: 2, pro tan: 4

tonoslfx Over a year ago

@Quassnoi: sorry my bad :D.. its from $db = new Database() class

Jason McCreary · Accepted Answer · 2011-04-27 16:16:27Z

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, 2011 at 16:16

Jason McCreary

73.3k23 gold badges140 silver badges177 bronze badges

Comments

Liv · Accepted Answer · 2011-04-27 16:17:40Z

0

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

answered Apr 27, 2011 at 16:17

Liv

6,1441 gold badge24 silver badges31 bronze badges

Collectives™ on Stack Overflow

mysql count multiple row where

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related