mysql multiple count in single table

Question

i have 2 query in one table t_push_member

SELECT
  DATE_FORMAT(`member_subscribe_at`, '%d-%m-%y') Tanggal,
  count(`member_msisdn`) 'total_reg'
FROM
  `t_push_member` 
WHERE
  `service_keycode`='nocan' 
GROUP BY
  Tanggal,
  'total_reg'
ORDER BY
  Tanggal

and

SELECT
  DATE_FORMAT(`member_unsubscribe_at`, '%d-%m-%Y') Tanggal,
  COUNT(`member_msisdn`) 'total_unreg'
FROM
  `t_push_member` 
WHERE
  `service_keycode`='nocan'
GROUP BY
  Tanggal, 'total_unreg'
ORDER BY
  Tanggal

how to make 2 queries into one query ?

You can use sub query to get it done in One query.

Ali
– Ali

2013-05-13 10:56:06 +00:00
Commented May 13, 2013 at 10:56 — Ali
– Ali, Commented May 13, 2013 at 10:56
What do you want the result to look like?

xagyg
– xagyg

2013-05-13 11:01:25 +00:00
Commented May 13, 2013 at 11:01 — xagyg
– xagyg, Commented May 13, 2013 at 11:01
i want tanggal, reg, unreg in one result

Maliq
– Maliq

2013-05-13 11:19:17 +00:00
Commented May 13, 2013 at 11:19 — Maliq
– Maliq, Commented May 13, 2013 at 11:19

Ankur Trapasiya · Accepted Answer · 2013-05-13 11:41:10Z

1

select * 
from 
( SELECT DATE_FORMAT(member_subscribe_at, '%d-%m-%y') Tanggal, count(member_msisdn),'REG' as Registered FROM t_push_member 
    where service_keycode='nocan' 
    group by Tanggal  

    UNION

    SELECT DATE_FORMAT(member_unsubscribe_at, '%d-%m-%Y') Tanggal, count(member_msisdn),'NOT_REG' as Registered FROM t_push_member 
    where service_keycode='nocan'
    group by Tanggal
)
order by  Tanggal

Learn more about UNION at http://www.w3schools.com/sql/sql_union.asp

edited May 13, 2013 at 11:41

answered May 13, 2013 at 10:57

Ankur Trapasiya

2,2004 gold badges22 silver badges43 bronze badges

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

8 Comments

Ankur Trapasiya Over a year ago

@Maliq : in UNION column names also have to be the same in all the queries that return columns. See the above updated answer. It will work.

Maliq Over a year ago

this my query SELECT ( SELECT count( * ) FROM t_push_member WHERE service_keycode = 'NOCAN' AND member_unsubscribe_at <> '0000-00-00 00:00:00' ) AS 'Member UNREG', ( SELECT count( * ) FROM t_push_member WHERE service_keycode = 'NOCAN' AND member_unsubscribe_at = '0000-00-00 00:00:00' ) AS 'Member REG' result only member reg and member unreg and I want to add a group by date

Ankur Trapasiya Over a year ago

@Maliq :Pl see the updated answer. There is already group by date in both of the queries and if you want to add any other group by then you can add it before order by. Hope this helps.

Maliq Over a year ago

thank you very much, its work!!. but I had to modify again for other purposes

Strawberry Over a year ago

But it's not a very good answer ;-) ... although, to be fair, it's hard to provide a more authoritative one without seeing the actual DDLs.

|

exussum · Accepted Answer · 2013-05-13 10:56:24Z

0

use "union" between the two - Instead of and

answered May 13, 2013 at 10:56

exussum

18.6k8 gold badges35 silver badges67 bronze badges

Collectives™ on Stack Overflow

mysql multiple count in single table

2 Answers 2

8 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related